If you want to get value from UserForm textbox using Excel VBA, you have come to the right place. Here, we will walk you through some easy steps to do the task smoothly.
What Is Excel VBA TextBox?
TextBoxes are used to collect input from users. They are a part of UserForms. The VBA TextBox controls are just one of many components of the UserForm. A text box can be displayed on the UserForm so the user can enter data. It also allows users to enter data on the worksheet using simple codes.
How to Use VBA to Get Value from Userform Textbox in Excel (With Easy Steps)
In the following article, we will describe 5 easy steps to get value from userform textbox using Excel VBA. Here, we used Excel 365. You can use any available Excel version.
Step-1: Bring VBA Window
Here, in the Student List worksheet, you can see a dataset. The dataset contains the Student Name, Math Score, Grade columns. Next, using VBA we will get value from the user textbox in this dataset.
- In the beginning, to bring out the VBA Editor window, we will go to the Developer tab >> select Visual Basic.
- Here, you can also bring up the VBA Editor window by using the keyboard shortcut ALT+F11.
- At this point, a VBA Editor window will appear.
- Furthermore, from the Inset tab >> select UserForm.
- Therefore, you can see the Userform.
- Along with that, you can see the Toolbox.
Step-2: Insert Labels on UserForm
In this step, we will insert Labels in the Userform box.
- First of all, from the Toolbox >> select Label.
- Then, we will draw the Label in the Userform.
- After that, we will edit the Label name according to our needs.
- Here, we give the Label name as Student Name.
- In a similar way, we add 2 more Labels and add names to them.
- Hence, you can see 3 Labels.
Step-3: Add Text Box to UserForm
In this step, we will add TextBox to our Userform.
- To do so, in the beginning, we will click on TextBox from the Toolbox.
- Then, we will draw a TextBox in the Userform.
- Furthermore, we will add names to our TextBox.
- To do so, we will click on the Textbox >> set the name as StudentName_TextBox.
- In a similar way, we inserted 2 more TextBoxes and gave a name to them.
Step-4: Insert Command Button
In this step, we will insert a Command Button into our TextBox.
- In the beginning, we will click on the Command Button from the Toolbox >> draw the Command Button.
- After that, we will edit the Command Button name.
- We edited the name to Submit.
- Therefore, you can see the Submit button.
- Afterward, we will click on the Run button.
- As a result, you can see the Userform now appears on the Worksheet.
- Next, we will use VBA code to get value from Userform to the dataset.
Step-5: Utilize VBA Macro to Get Value from UserForm Textbox
In this step, we will use VBA to get value from userform textbox.
- First of all, we will double-click on the Submit button.
- Therefore, you can see a private sub appears in the VBA Editor window.
- Afterward, we will type the following code.
Private Sub CommandButton1_Click() TextBoxValue1 = StudentName_Textbox.Text Sheets("Student List").Range("B5").Value = TextBoxValue1 TextBoxValue2 = StudentMath_Score.Text Sheets("Student List").Range("C5").Value = TextBoxValue2 TextBoxValue3 = Math_Grade.Text Sheets("Student List").Range("D5").Value = TextBoxValue3 End Sub
- After that, click on the Run button.
- Here, you can type the following code as well, and click on the Run button.
Private Sub CommandButton1_Click() Dim LR As Long LR = Worksheets("Student List").Cells(Rows.Count, 1).End(xlUp).Row + 4 Range("B" & LR).Value = StudentName_Textbox.Value Range("C" & LR).Value = StudentMath_Score.Value Range("D" & LR).Value = Math_Grade.Value End Sub
- Therefore, you can see the UserForm in the worksheet.
- Next, we will give the Student Name in the first Textbox.
- Along with that, we will give the Student Math Score in the second Textbox.
- Furthermore, we will give the Math Grade in the 3rd Textbox.
- Finally, click Submit.
- Therefore, you can see the result in cells B5, C5, and D5.
- Here, you can modify the code and for other cells as well, and you can complete the table according to your needs.
You can download the above Excel file to practice the explained method.
Download Practice Workbook
You can download the Excel file and practice while you are reading this article.
Here, we tried to show you 5 easy steps for Excel VBA get value from UserForm textbox. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.
- Excel VBA: Show Userform in Full Screen
- Excel VBA: UserForm Image from Worksheet
- How to Use Excel UserForm as Date Picker
- Excel VBA: Print UserForm to Fit on a Page
- Excel VBA to Format Textbox Number with UserForm
- How to Create a UserForm: an Overview
- How to Create Toggle Button on Excel VBA UserForm