VBA TextBox

Excel VBA TextBox

Textbox 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 textbox option from user form controls in VBA or in worksheet we can select it from the design tab.

VBA TextBox 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 codingVBA Coding VBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. It helps us immensely, especially when we need to get 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 of VBA text boxes.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA TextBox (wallstreetmojo.com)

How to Insert TextBox in VBA UserForm?

You can download this VBA Text Box Template here – VBA Text Box Template

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.

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

From this Controls toolbox, select “TextBox” and drag on the User Form.

With this, we can see many properties associated with this text box.

TextBox Properties

Press the F4 key to see the Properties of the Text Box.

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

Example of using TextBox in Userform

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 the “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 the 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 the 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 the 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 “Employees Sheet.”

Step 13: Now come back to the visual basic editor. Inside the button, click subroutine in VBASubroutine In VBA SUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read more first to 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 “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 the salary part. For this, we need to access to text box named “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