How to Use VBA to Get Value from Userform Textbox in Excel

What Is Excel VBA TextBox?

In Excel VBA, a TextBox is a user interface element that allows users to input data.

The sample worksheet Student List dataset contains the Student Name, Math Score, Grade columns. We will get value from the user textbox in this dataset using VBA.

Step 1 – Bring up the VBA Window

• Go to the Developer tab >> select Visual Basic.
• You can also bring up the VBA Editor window by using the keyboard shortcut ALT+F11.

• A VBA Editor window will appear.
• From the Inset tab >> select UserForm.

• The Userform and a Toolbox will appear.

Step 2 – Insert Labels on UserForm

• From the Toolbox >> select Label.
• Draw the Label in the Userform.

• Edit the Label name according to your needs.
• We edited the Label name as Student Name.

• Add 2 more Labels and add names to them.
• You can see 3 Labels.

Step 3 – Add Text Box to UserForm

• Click on TextBox from the Toolbox.
• Draw a TextBox in the Userform.

• Add names to the TextBox.
• Click on the Textbox >> set the name as StudentName_TextBox.

• Insert 2 more TextBoxes and name them.

Step 4 – Insert Command Button

• Click on the Command Button from the Toolbox >> draw the Command Button.

• Edit the Command Button name.
• We edited the name to Submit.
• You can now see the Submit button.

• Click on the Run button.

• The Userform now appears on the Worksheet.
• Use VBA code to get value from Userform to the dataset.

Step 5 – Utilize VBA Macro to Get Value from UserForm Textbox

• Double-click on the Submit button.

• A private sub appears in the VBA Editor window.

• Insert 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

• Click on the Run button.
• You can enter 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

• The UserForm appears in the worksheet.
• Enter the Student Name in the first Textbox.
• Enter the Student Math Score in the second Textbox.
• Enter the Math Grade in the 3rd Textbox.
• Click Submit.

• You can see the result in cells B5, C5, and D5.
• You can modify the code, and for other cells as well, to complete the table as required.

Download Practice Workbook

Related Articles

• Excel VBA: Show Userform in Full Screen
• Excel VBA: UserForm Image from Worksheet
• How to Use Excel UserForm as Date Picker
• Excel VBA to Format Textbox Number with UserForm
• How to Create Toggle Button on Excel VBA UserForm