Excel VBA TextBox

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

How to Insert TextBox in VBA UserForm?

To insert a text box first, we need to insert a UserForm. To insert the UserForm, go to Insert > UserForm.

As soon as you click on UserForm, we will use UserForm just like another module.

Click on UserForm to see the controls of the UserForm.

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

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

TextBox Properties

Press the F4 key to see the properties of the TextBox.

As we can see, the text box has a name, color, and border like this many. Now, give a proper name to refer to this text box while coding easily.

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 UserForm, draw the 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: 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 “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 “SalaryTextBox.”

Step 9: Insert the “Command Button” from the ToolBox.

Step 10: Change the text of the command button to “Submit.”

We have completed the UserForm design part. Next, we need to write the code to store the data entered in this UserForm. As of now, run the UserForm by pressing the F5 key. We should see a UserForm like this.

Step 11: Change the caption of the UserForm 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 one below.

What should happen when you click on the “Submit” button? First, we need to mention the tasks in the VBA code. In this project, we aim to store the data entered in the text box as soon as we click 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 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

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

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: We need to store the “Employee ID” in the second column. So, we may obtain this by accessing the text box “EmpIDTextBox.”

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 a text box named “SalaryTextBox.”

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

We have completed the coding part as well. Now, run the code using the F5 key. We should see a UserForm like the one below.

As of now, all the boxes are empty.

Fill in the details first.

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

Like this, you can keep entering the data and press the “Submit” button. It is a simple data entry UserForm with a text box.

This article has been a guide to VBA TextBox. Here, we learn how to insert and use textbox control in UserForm through Excel VBA, practical examples, and a downloadable template. Below you can find some useful Excel VBA articles: –

  • VBA Close UserFormExcel VBA ArrayListInsert Row using VBAUsing Not Equal in VBA