VBA Text Box

Excel VBA Text Box

Text box is simply like a box which is used to get input from a user, text boxes are a part of user forms and in developer tab in any excel worksheet, If we want to make text boxes in a user form we can select the text box option from user form controls in VBA or in worksheet we can select it from the design tab.

VBA Text Box is one of the controls of the many controls of the User Form. By displaying the text box on the user form we can ask them to enter the data into the text box and data entered by the user can be stored to the worksheet with simple codes.

User forms are very attractive in VBA coding. It helps us immensely especially when we need to get the input from the users. With user forms, we have many controls and to get the input value from the users “Text Box” is the ideal option in a user form. By putting a text box on the user form we can actually tell the user to enter the required value in the text box we are displaying. If you have no idea about VBA text boxes then this article will take a tour on VBA text boxes.

How to Insert Text Box?

In order to insert a text box first, we need to insert user form. To insert user form go to Insert > User Form.

As soon you click on User Form we will User Form just like another module in VBA.

Click on User Form to see the Controls of the User Form.

From this Controls toolbox select “Text Box” and drag on the User Form.

With this, we can see many properties associated with this text box. Press F4 key to see the Properties of the Text Box.

As we can see text box has Name, colour, Border like this many. Now give a Proper Name so that we can easily refer to this text box while coding.

Example

We will conduct one of the projects with VBA text. We will create a data entry user form to store the details of the employees.

Step 1: On the user form draw Label.

Step 2: Change the default text of the label to “Employee Name”.

Step 3: In front of the Label draw a text box.

Step 4: Give a proper name to the text box as “EmpNameTextBox”.

Step 5: Now draw one more Label below the first label and enter the text as “Employee ID”.

Step 6: In front of the second label draw one more text box and name it as “EmpIDTextBox”.

Step 7: Draw one more label and enter the text as “Salary”.

Step 8: Draw one more text box in front of “Salary” label and name it as “SalaryTextBox”.

Step 9: Now insert “Command Button” from the ToolBox.

Step 10: Change the text of the Command Button as “Submit”.

Ok, we are done with User Form design part. Now we need to write the code to store the data entered in this user form. As of now run the user form by pressing F5 key we should see a user form like this.

Step 11: Change the Caption of the User Form in the Properties window.

Step 12: Now double click on the Submit Command Button.As soon as you double click you will see this auto sub procedure like the below.

This is when you click on the Submit button what should happen. We need to mention the tasks in VBA code. In this project, our aim is to store the data entered in the text box as soon as we click on the Submit Button.

For this first create a Template like this in the worksheet named as “Employees Sheet”.

Step 13: Now come back to the visual basic editor. Inside the button click sub procedure first determine the last used row by using the below code.

Code:

Private Sub CommandButton1_Click()

   Dim LR As Long

   LR = Worksheets("Employee Sheet").cell(Rows.Count, 1).End(xlUp).Row + 1

End Sub

Step 14: First thing is in the first column we will store Employee Name. So for this, we need to access the text box named as “EmpNameTextBox”.

Code:

Private Sub CommandButton1_Click()

   Dim LR As Long

   LR = Worksheets("Employee Sheet").cell(Rows.Count, 1).End(xlUp).Row + 1

   Ramge("A" & LR).Value = EmpNameTextBox.Value

End Sub

Step 15: In the second column we need to store Employee ID. So this will be obtained by accessing the text box named “EmpIDTextBox”.

Code:

Private Sub CommandButton1_Click()

   Dim LR As Long

   LR = Worksheets("Employee Sheet").cell(Rows.Count, 1).End(xlUp).Row + 1

   Ramge("A" & LR).Value = EmpNameTextBox.Value

   Ramge("B" & LR).Value = EmpIDTextBox.Value

End Sub

Step 16: At last we need to store salary part, for this we need to access to text box named as “SalaryTextBox”.

Code:

Private Sub CommandButton1_Click()

   Dim LR As Long

   LR = Worksheets("Employee Sheet").cell(Rows.Count, 1).End(xlUp).Row + 1

   Ramge("A" & LR).Value = EmpNameTextBox.Value

   Ramge("B" & LR).Value = EmpIDTextBox.Value

   Range("C" & LR).Value = SalaryTextBox.Value

End Sub

Ok, we are done with the coding part as well. Now run the code using F5 key we should see a User Form like the below.

As of now, all the boxes are empty.

Fill the details first.

Now click on the “Submit” button, it will store the data to the worksheet.

Like this, you can keep entering the data and hit and submit button. This is the simple data entry user form with text box.

Recommended Articles

This has been a guide to VBA Text Box. Here we learn how to insert and use text box control in user form through excel VBA along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

• Excel VBA Close UserForm
• Examples of VBA IIF Function
• Find and Replace in Excel VBA
• VBA ArrayList
• Excel Proper Formula
• Delete Column using VBA Code
• Insert Row using VBA
• Weekday in VBA
• Using Not Equal in VBA