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 CodingVBA 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.

VBA TextBox

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.

Insert Userform

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

Userform controls

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

VBA Text Box Userform 1-1

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

VBA Text Box control

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.

TBox Properties

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.

Change Name Property

Example of using TextBox in Userform

We will conduct one of the projects with VBA textVBA TextText is a worksheet function in excel but it can also be used in VBA while using the range property. It is similar to the worksheet function and it takes the same number of arguments. These arguments are the values which needs to be converted.read more. We will create a data entry user formCreate A Data Entry User FormThe form in Excel simplifies data entry by allowing us to view, add, edit, and delete one record in a horizontal orientation. It is a hidden feature that isn't accessible through the ribbon tools. This functionality must be added using the quick access toolbar option under the File tab.read more to store the details of the employees.

Step 1: On the user form, draw Label.

VBA TextBox step 1

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

VBA TextBox step 2

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

VBA TextBox step 3

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

VBA TextBox step 4

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

VBA TextBox step 5

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

VBA TextBox step 6

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

VBA TextBox step 7

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

VBA TextBox step 8

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

VBA TextBox step 9

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

VBA TextBox step 10

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.

VBA TextBox step 11

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

VBA TextBox step 11

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.

VBA Text Box step 12

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.”

VBA Text Box step 12.1

Step 13: Now come back to the visual basic editor. Inside the button, click subroutine in VBASubroutine In VBASUB 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
VBA Text Box step 13

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
VBA Text Box step 14

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
VBA Text Box step 15

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
VBA Text Box step 16

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

Userform Design

As of now, all the boxes are empty.

Fill the details first.

Enter Details

Now click on the “Submit” button. It will store the data on the worksheet.

Output

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

Recommended Articles

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

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>