How to Populate Textbox Based on Combobox Selection on UserForm in Excel?


UserForms in Microsoft Excel offer a fun approach to get user input and data collection. They include numerous controls, like textboxes, buttons, checkboxes, and combo boxes, which make it simple for users to enter or choose data. The ability to fill a textbox with data from a combobox is a typical requirement in UserForms. This article will walk you through establishing a UserForm in Excel and show you how to dynamically fill a textbox based on the choice you make in a combobox. By implementing these procedures, you can improve the user experience by having pertinent data be filled in automatically when users make choices.

Populate Textbox Based on Combobox Selection on UserForm

Here we will first insert a user form, then add VBA code to the form to complete the task. So let us see a simple process to know how you can populate textboxes based on combobox selection on a user form in Excel.

Step 1

Consider any Excel sheet. First, right-click on the sheet name and select View Code to open the VBA application.

Right Click > View Code.

Step 2

Then click Insert and select UserForm. Then draw a Combo box control and a text box control.

Insert > UserForm > Combo Box Control > Text Box Control.

Step 3

Then right-click the user form, select View code, and copy the below code into the text box.

Right Click > View Code > Copy.

Code

Dim xRg As Range
Private Sub UserForm_Initialize()
   Set xRg = Worksheets("Sheet1").Range("A2:B8")
   Me.ComboBox1.List = xRg.Columns(1).Value
End Sub
Private Sub ComboBox1_Change()
   Me.TextBox1.Text = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, xRg, 2, False)
End Sub

Step 4

Then exit the VBA by using Alt + Q. Then click on developer, then click on insert, and select the command button under ActiveX controls.

Alt + Q > Developer > Insert > Command Button.

Step 5

Then draw a command button. Then right-click on the sheet name, select View Code, and copy the below code into the text box.

Draw > Right Click > View Code > Copy.

Code

Private Sub CommandButton1_Click()
   Dim frm As UserForm1
   Set frm = New UserForm1
   frm.Show
End Sub

Step 6

Then exit design mode. Then click on the command button. Then you can see all the names collected in the combo box.

When selecting a name from the combo box, the corresponding staff number will be populated into the textbox automatically, as shown in the below screenshot.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can populate textboxes based on combobox selection on a user form in Excel to highlight a particular set of data.

Updated on: 07-Sep-2023

500 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements