How to Get an Excel VBA Input Box with a Drop-Down List: 2 Methods

The image below shows an input box with a drop-down list of names.

How to Launch VBA Macro Editor in Excel

To run a VBA code, we first need to write the code in the VBA Macro Editor.

Steps:

Go to Developer Tab.
Open Visual Basic.

Select Insert >> Module in the macro editor.

How to Insert a UserForm in Excel

To get the input into a drop-down list, we have to first build a UserForm.

Steps:

Open VBA Macro Editor.
Go to Insert >> UserForm.
You can customize the

The above video shows how we added different elements to our UserForm to customize it according to our needs.

Our form contains a ComboBox and three TextBoxes with Labels beside them. We also changed the background of the form.

Method 1 – Adding Drop-Down List Values Using .AddItem Command

Steps:

Open the Visual Basic window.
Enter the following formula in UserForm1.

Private Sub UserForm_Initialize()
'defining a variable as worksheet type variable
Dim ws As Worksheet
'setting the .AddItem Command sheet as the value for the variable
Set ws = ThisWorkbook.Sheets(".AddItem Command")
'setting the B5:B15 range as the value of the Rng variable
Set Rng = ws.Range("B5:B15")
'adding Values to the Name ComboBox
For i = 1 To Rng.Rows.Count
    UserForm1.ComboBox1.AddItem (Rng.Cells(i, 1))
Next i
End Sub

Enter the following code to add information of the selected employee to the UserForm TextBoxes.

Private Sub ComboBox1_Change()
'declaring two variables as worksheet and string type
Dim ws As Worksheet
Dim Choice As String
'initializing the values of the variables
Set ws = ThisWorkbook.Sheets(".AddItem Command")
Set Rng = ws.Range("B5:E15")
Choice = UserForm1.ComboBox1.Value
'running for loop to show the information of the selected name_
'_from the combobox in the subsequent text boxes of the UserForm
For i = 1 To Rng.Rows.Count
    If Choice = Rng.Cells(i, 1) Then
        For j = 2 To Rng.Columns.Count
            Me("TextBox" & j).Value = Rng.Cells(i, j)
        Next j
    End If
Next i
End Sub

Select the name of an employee from the drop-down list of the combo box.

VBA Breakdown 1

Private Sub UserForm_Initialize()

The “UserForm_Initialize()” is an “Event” in VBA. The subsequent code under this sub will be applied when we run the UserForm.

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(".AddItem Command")
Set Rng = ws.Range("B5:B15")

Here, we declared a variable “ws” and set its datatype to “Worksheet”. Then, we added the “.AddItem Command” as its value. We also set the value of the “Rng” variable to all the values from the “B5:B15” range or the values from the “Name” column of the dataset.

For i = 1 To Rng.Rows.Count
    UserForm1.ComboBox1.AddItem (Rng.Cells(i, 1))
Next i

We ran a For Loop throughout the rows of the dataset.

VBA Breakdown 2

Private Sub ComboBox1_Change()

The “ComboBox1_Change()” is termed as an “Event” in VBA. The subsequent code under this sub will be executed when we make a change to the ComboBox of the UserForm or simply select a name from the box.

Dim ws As Worksheet
Dim Choice As String
Set ws = ThisWorkbook.Sheets(".AddItem Command")
Set Rng = ws.Range("B5:E15")
Choice = UserForm1.ComboBox1.Value

We declared three variables, namely “ws”, “Choice” and “Rng”.
We set the “.AddItem Command” sheet as the value of the “ws” variable.
We created the range of the entire dataset and assigned it as the value of the “Rng” variable. The “Choice” variable holds the selected value of the ComboBox1 of the UserForm1.

For i = 1 To Rng.Rows.Count
    If Choice = Rng.Cells(i, 1) Then
        For j = 2 To Rng.Columns.Count
            Me("TextBox" & j).Value = Rng.Cells(i, j)
        Next j
    End If
Next i

We ran two For Loops on the dataset. The first For Loop runs through the first column or the Name column of the dataset and then matches the value with the selected value from the combo box. The next For Loop explored the columns of the dataset except for the Name column and extracted the information related to the name of the employee that we selected from the ComboBox1 from those columns. They were added to the text boxes of UserForm1.

Read More: Excel VBA: Input Box with Options

Method 2 – Adding Drop-Down List Values Using RowSource Property

Steps:

Add the drop-down list to the ComboBox using the RowSource
Add the names in the ComboBox from a range in the worksheet.

Private Sub ComboBox1_Change()
'declaring two variables as worksheet and string type
Dim ws As Worksheet
Dim Choice As String
'initializing the values of the variables
Set ws = ThisWorkbook.Sheets("Row_Source Property")
Set Rng = ws.Range("B5:E15")
Choice = UserForm2.ComboBox1.Value
'running for loop to show the information of the selected name_
'_from the combobox in the subsequent text boxes of the UserForm
For i = 1 To Rng.Rows.Count
    If Choice = Rng.Cells(i, 1) Then
        For j = 2 To Rng.Columns.Count
            Me("TextBox" & j).Value = Rng.Cells(i, j)
        Next j
    End If
Next i
End Sub

VBA Breakdown

This is also a ComboBox_Change event like the one we have mentioned above. This code also allows users to select a name from the combo box and write the related information in the text boxes. The only difference in this code is that here, we took the Row_Source Property sheet as the value of the ws

We selected the ComboBox from the UserForm2.
We entered the range “B5:B15” in the RowSource Property box. This will add the names from the Name column into the drop-down list.

Run the UserForm2 in the Visual Basic Window.
Chose the name of an employee from the drop-down list.
Get the data for the employee in the text boxes.

Method 3 – Using ComboBox to Create Drop-down List

Steps:

Go to Developer >> Insert >> ActiveX Controls >> ComboBox

Insert the ComboBox into the sheet.
Right-Click on the box
Select View Code from options.
Add the following code to the module.

Private Sub ComboBox1_Change()
'declaring variable
Dim ws As Worksheet
'setting values to the variables
Set ws = ThisWorkbook.Sheets("ComboBox")
Set Rng = ws.Range("B5:B15")
'adding names to the combobox from the Name column
For i = 1 To Rng.Rows.Count
    ComboBox1.AddItem (Rng.Cells(i, 1))
Next i
'setting values to the Rng2 and Choice variables
Set Rng2 = ws.Range("B5:E15")
Choice = ComboBox1.Value
'running for loop to highlight the information of the_
'_selected name from the combobox
For i = 1 To Rng2.Rows.Count
    If Choice = Rng2.Cells(i, 1) Then
        For j = 1 To Rng2.Columns.Count
            Rng.Cells(i, j).Interior.Color = vbGreen
        Next j
    End If
Next i
End Sub

VBA Breakdown

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("ComboBox")
Set Rng = ws.Range("B5:B15")

Here, we have a “ComboBox1_Change” event. This event will occur when we change or select any value from the combo box inserted from the ActiveX Controls. We declared the “ws”, “Rng” variables and set their values.

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("ComboBox")
Set Rng = ws.Range("B5:B15")

Here, we ran a For Loop throughout the first column of the dataset. The loop takes the name from the “Name” column in each instance and adds the input combo box with the .AddItem command.

Set Rng2 = ws.Range("B5:E15")
Choice = ComboBox1.Value
For i = 1 To Rng2.Rows.Count
    If Choice = Rng2.Cells(i, 1) Then
        For j = 1 To Rng2.Columns.Count
            Rng.Cells(i, j).Interior.Color = vbGreen
        Next j
    End If
Next i
End Sub

Here, we have two new variables namely, “Rng2” and “Choice”. The “Rng2” variable shows the entire dataset as its value. We selected “Choice” as the value in the combo box. We ran two For Loops. One ran through the first column and matched the names with the name selected from the combo box. The second loop simply marks the subsequent columns of the matched row with green.

Run the code above in the VBA module.
The values in the Name column will be added to the drop-down list of the combo box.
If we select a name from the list, the entire row will be highlighted.

Method 4 – Inserting VBA InputBox in Excel

Steps:

Enter the following code in InputBox:

Sub VBAInputBox()
'declaring variables
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("InputBox")
'inserting an InputBox with prompt and title
Employee_Name = InputBox(Prompt:="Please write the name of the employee", Title:="Employee Selection")
'setting the value of the Rng variable
Set Rng = ws.Range("B5:E15")
'running for loop to highlight the information of the name written in the InputBox
For i = 1 To Rng.Rows.Count
    If Employee_Name = Rng.Cells(i, 1) Then
        For j = 1 To Rng.Columns.Count
            Rng.Cells(i, j).Interior.Color = vbGreen
        Next j
    End If
Next i

Write a name in the box. The code highlights the information about the employee from the dataset.

VBA Breakdown

This code uses the name entered by the user, then highlights the matching row in the range “B5:E15” in the “InputBox” worksheet by turning each cell’s interior color green.

Employee_Name = InputBox(Prompt:="Please write the name of the employee", Title:="Employee Selection")

The only difference about this code is that we take the input from an InputBox. This line creates an InputBox with a prompt message “Please write the name of the employee” and the title “Employee Selection”. The input value will be stored in a variable named “Employee_Name”.

Enter an InputBox into the code and get a value from the InputBox.

The InputBox’s value is the name of the employee.

Go to Developer >> Macros
Run the macro named VBAInputBox.
Enter the name of the employee.
Click OK.

Method 5 – Creating a Data Validation Drop-Down List using VBA

Steps:

Add a drop-down list in a particular cell of a sheet by using the following VBA code:

In this instance, we will add a drop-down list in a particular cell of a sheet by using the following VBA code.

Sub DataValidationDropDown()
'setting the cell where the list will appear
With Range("G5").Validation
'adding values to the drop-down list
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$B$5:$B$15"
.InCellDropdown = True
End With
End Sub

VBA Breakdown

Sub DataValidationDropDown()
With Range("G5").Validation

This line uses the “With” statement to refer to the cell’s validation object and sets cell “G5” as the location of the drop-down list.

.Add xlValidateList, xlValidAlertStop, xlBetween, "=$B$5:$B$15"
.InCellDropdown = True

This line updates the validation object for cell “G5” to include a drop-down menu. With the “xlValidateList” option, it changes the type of validation to a list of values. If the user enters an invalid value, an error message is shown using the “xlValidAlertStop” option. The value must fall between two values according to the “xlBetween” option (which in this case is not specified). The dollar sign is used to make the reference absolute and assign the list of values to the range “B5:B15”. The last line specifies that a drop-down list, rather than a pop-up window, should show in the cell.