VBA TextBox

What is Excel VBA TextBox?

VBA TextBox is a graphical user interface (GUI) control that allows users to input and display text within a VBA UserForm. It is an interactive field where users can enter data or view information. VBA TextBoxes are commonly used in VBA UserForms to create custom dialog boxes, data entry forms, and interactive interfaces that enhance the functionality and usability of Excel applications.

After retrieving the value from the TextBox, the code calculates the square using “^” and displays the result in a message box.

After you enter any desired number in the textbox and click the “Calculate Button,” you will see a message box with the appropriate result.

How to Insert TextBox in VBA UserForm?

TextBox Properties

The VBA TextBox control has several properties that can be customized to suit specific requirements.

Some common VBA TextBox properties include:

Property	Description
Name	Specifies the name of the TextBox control for referencing in the VBA code.
Caption	Sets the label or title displayed above the TextBox.
Value	Represents the current text in the TextBox.
Font	Allows customization of the text’s font family, size, and style within the TextBox.
Width	Determines the width of the TextBox control.
Height	Determines the height of the TextBox control.
MaxLength	Limits the maximum number of characters allowed in the TextBox.
MultiLine	Determines whether the TextBox can contain multiple lines of text.
Enabled	Controls whether the TextBox is enabled for user interaction.
Visible	Controls whether the TextBox is visible on the UserForm.
BackColor	Sets the background color of the TextBox.
ForeColor	Sets the foreground color (text color) of the TextBox.
PasswordChar	Sets a character to mask the input (e.g., for password entry).

Note: Some VBA TextBox properties may not be applicable when working with multi-line VBA TextBoxes or in specific scenarios.

Examples

Example #1

In this example, we will capture the VBA TextBox input, store it in a new row in “Sheet1,” clear the TextBox, and close the UserForm.

• Step 1: Create a subroutine named “CommandButton1_Click” within the UserForm’s code module. This subroutine will execute when the button is clicked.

• Step 2: Next, we declare a variable named ws to hold a reference to a worksheet. The Set statement in VBA assigns the “Sheet2” worksheet to the ws variable. It will allow us to work with the specified worksheet throughout the procedure.

• Step 3: Now, we declare a variable named “lastRow” of VBA data type Long. This variable will hold the row number where the new data will be stored.

• Step 4: We use “ws.Rows.Count”  to return the total number of rows in the specified worksheet. ws.Cells(ws.Rows.Count, “A”).End(xlUp) finds the last cell with data in column A by starting from the last row and moving upwards until it encounters data. Adding 1 to this row number gives us the next available row for data entry.

• Step 5: In the next line, we assign the value of the TextBox1 control on the UserForm to the cell in the lastRow and column 1 (column A) of the specified worksheet (ws). It effectively stores the entered data from TextBox in the worksheet.

• Step 6: After the data is stored, we code “TextBox1.Value = “”, which clears the content of the TextBox1 control on the UserForm. It prepares the TextBox for the next data entry.

• Step 7: Next, we close the user form. Using “Unload Me” will close the UserForm that contains this code. This step completes storing the data, clearing the TextBox, and closing the UserForm.

• Step 8: Save the Userform and create a new module to display a UserForm named “UserForm1” in our example.

When the subroutine is executed, it shows the specified UserForm on the screen.

• Step 9: Now, save the module and exit the VBE. Press Alt + F8 to open the macro window and select “ShowDataEntryForm,” and click on Run.

• Step 10: Once you execute the Macro, you will see a VBA TextBox open, allowing you to enter any data in the text box.

• Step 11: In the text box, provide any text. For example, enter “John” and then click on “CommandButton1.”

This above procedure is triggered when we click the CommandButton on the UserForm. It stores the data from the VBA TextBox in the specified worksheet (in this case, the name “John”), clears the VBA TextBox for the next entry, and closes the UserForm.

Here is the full code:

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Worksheets(“Sheet1”)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row + 1
ws.Cells(lastRow, 1).Value = TextBox1.Value
TextBox1.Value = “” ‘ Clear the TextBox after storing data
Unload Me ‘ Close the UserForm
End Sub

Private Sub UserForm_Click()
End Sub

Example #2

In this example, we will create a User form with VBA TextBox that allows us to enter inputs such as Name, Age, and Marks and store the entered information in the Excel worksheet.

First, we create a user form with the required text boxes and command buttons using the steps mentioned in Section 2(How to Insert TextBox in VBA UserForm?) above.

• Step 1: Create a subroutine named SubmitButton_Click within the UserForm’s code module. This subroutine will execute when the “Submit” button is clicked.

• Step 2: Next, we declare a VBA variable named ws of type Worksheet. This variable will be used to refer to the “Sheet2” worksheet, where the data will be stored.

• Step 3: Set the ws variable to reference the “Sheet2” worksheet. It ensures that the subsequent code interacts with the correct worksheet.

• Step 4: Next, declare a variable named lastRow of type Long. This variable will store the row number where the new data should be added.

Calculate the last used row number in column A of “Sheet2” and add 1 to get the next available row for data entry.

• Step 5: Now, store TextBox Values in Worksheet:

Assign the value of the NameTextBox control to the first column (column A) of the next available row on “Sheet2.”

Assign the value of the AgeTextBox control to the second column (column B) of the same row.

Assign the value of the MarksTextBox control to the third column (column C) of the same row.

• Step 6: Next, clear TextBox Values:

Clear the text content of the NameTextBox control after data entry.

Clear the text content of the AgeTextBox control after data entry.

Clear the text content of the MarksTextBox control after data entry.

• Step 7: Next, create an Exit Button Click Event – Private Sub ExitButton_Click()

Define a new subroutine named ExitButton_Click to handle the “Exit” button functionality.

Unload the UserForm using the Unload Me statement, effectively closing and unloading the UserForm.

• Step 8: Save the Userform and create a new module to display a UserForm named “UserForm2” in our example.

When the subroutine is executed, it shows the specified UserForm on the screen.

• Step 9: Now, save the module and exit the VBE. Press Alt + F8 to open the macro window and select “ShowDataEntryForm2” and click on Run.

• Step 10: Once you execute the Macro, you will see a VBA TextBox open allowing us to enter the data such as Name, Age and marks.

• Step 11: Once the data is entered, proceed by clicking the “Submit” button, which will then save the data into the corresponding cells of the Excel worksheet.

You have the flexibility to input a multitude of details, and the TextBox UserForm will remain open until you choose to exit by clicking the designated button.

After you input the data, you can click on the Exit button to close the user form.

Here is the full code:

Private Sub SubmitButton_Click()
Dim ws As Worksheet
Set ws = Worksheets(“Sheet2”)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row + 1
ws.Cells(lastRow, 1).Value = NameTextBox.Value
ws.Cells(lastRow, 2).Value = AgeTextBox.Value
ws.Cells(lastRow, 3).Value = MarksTextBox.Value
NameTextBox.Value = “”
AgeTextBox.Value = “”
MarksTextBox.Value = “”
End Sub

Private Sub ExitButton_Click()
Unload Me
End Sub

Private Sub UserForm_Click()
End Sub

Important Things to Note

• Always validate the data entered in VBA TextBoxes to ensure it meets the required VBA TextBox format and constraints.
• Implement proper VBA error handling in your code to handle unexpected user inputs and prevent crashes.
• Manage the focus of controls, especially if you have multiple VBA TextBoxes in the UserForm, to provide a smooth user experience.
• Understand the various events associated with VBA TextBoxes (e.g., Change, Enter, Exit) to trigger actions based on user interactions.
• VBA TextBox value refers to the text or content entered by the user into a VBA TextBox control within a UserForm. This value can be accessed and manipulated using the Value property in VBA of TextBox.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA TextBox, with its features and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA TextBox. Here we learn how to insert TextBox in Excel VBA UserForm, with its usage, properties, examples & points to remember. You can learn more from the following articles –

• VBA Text
• VBA Write Text File
• VBA IsNumeric