Excel VBA Input Box with Drop Down List (2 Examples)

In this article, we will discuss how to get an Excel VBA input box with drop down list. Users often need to insert input from multiple options. In that case, a drop-down list can offer a range of values to select from. In this article, we help them create an input box with multiple options.

The above overview image shows an input box with a drop down list of names.

How to Launch VBA Macro Editor in Excel

In order to run any VBA code, we first need to write or edit the code in the VBA Macro Editor. Follow the simple steps below to open up the VBA Macro Editor.

• Go to Developer Tab >> Visual Basic.
• This will open the Visual Basic window.

• Select Insert >> Module in the macro editor.

• As a result, an empty module will appear on the screen where you can write the code.

Is It Possible to Add Drop Down List in Input Box in Excel?

It is not possible to add a drop-down list to the InputBox that we normally insert from the VBA code. However, we can add a drop-down list to a UserForm ComboBox and then take the ComboBox as the input box.

We can also add a Data Validation Drop-Down List to a cell from the Data Validation tab or use VBA codes.

How to Insert a UserForm in Excel

In order to get the input into a drop-down list, first we will have to build a UserForm. Follow the steps below to do so-

• Open VBA Macro Editor.
• Go to Insert >> UserForm.

Then, we can customize the UserForm according to our needs.

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.

Excel VBA Input Box with Drop Down List: 2 Ways with UserForm

Here, we have a dataset of employees that contains the Name, ID, Department, and Salary of each employee. We will select a name from the input drop-down list of a UserForm or a Combo Box and we will get all the information related to that employee either in a text box of the UserForm or in a highlighted form in the Excel sheet. To do so, we will first use a UserForm and then a Combo Box from the ActiveX Controls.

In this method, we will create a UserForm and add a ComboBox to that form. Then, we will add a drop-down list to that ComboBox using two methods. First, we will use the .AddItem property of the ComboBox to add the options. Then, we will use the RowSource property to insert the option in the ComboBox list.

Example 1. Adding Drop-Down List Values Using .AddItem Command

There are two codes in UserForm1. The first code which is UsertForm_Initialize event, adds the drop-down list in the ComboBox by using the .AddItem command. The following code adds the information related to the name that is selected in the ComboBox to the TextBoxes.

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

• This code will run as soon as we run the UserForm1.
• It will add values to the drop-down list of the ComboBox of the UserForm1 from the Name column in the dataset.

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

• This code will add the information of the selected employee to the UserForm TextBoxes.

• As the video suggests, we ran the UserForm1 in the Visual Basic window.
• Then, we selected the name of an employee from the drop-down list of the combo box.
• In return, we get the information related to that employee in the text boxes.

Private Sub UserForm_Initialize()

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

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

Private Sub ComboBox1_Change()

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

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

Read More: Excel VBA: Input Box with Options

Example 2. Adding Drop-Down List Values Using RowSource Property

In this instance, we will add the drop-down list to the ComboBox using the RowSource property. In this process, we will 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

• As illustrated in the video, we first selected the ComboBox from the UserForm2.
• Then, from the property window, we typed the range “B5:B15” in the RowSource Property box.
• This will add the names from the Name column into the drop-down list.

• We will run the UserForm2, same as the UserForm1 in the above video in the Visual Basic Window.
• Then, we chose the name of an employee from the drop-down list.
• In exchange, we receive the data pertaining to the employee in the text boxes.

Read More: How to Use VBA Input Box with Buttons in Excel

How to Use Combobox to Create Drop-down List in Excel

In this method, we will add a drop-down list to the combo box that we will insert using the ActiveX Controls feature. Follow the steps below to add a combo box.

• Go to Developer >> Insert >> ActiveX Controls >> Combo Box

• Insert the Combo Box into the sheet.
• Right-Click on the box and from the options choose View Code.
• Then, add the following code to the module that will appear on the screen.

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

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

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

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

• We run the code above in the VBA module.
•  As a result, the values in the Name column will be added to the drop-down list of the combo box.
• Now, if we select a name from the list, the entire row will be highlighted.

Read More: How to Create Yes-No InputBox with Excel VBA

How to Insert VBA InputBox in Excel

The following code inserts an InputBox and prompts the users to write a name in the box. In return, the code highlights the information about the employee from the dataset.

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

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

Here, we will incorporate an InputBox into our code and then get value from that InputBox.

The value that the InputBox will receive is the name of the employee.

• Go to Developer >> Macros
• Run the macro named VBAInputBox.
• Write down the name of the employee whose information you want.
• Click OK.

As we insert the name, the code will highlight the information related to that employee from the dataset.

How to Create Data Validation Drop-Down List with VBA in Excel

The code below adds a Data Validation drop-down list in a particular cell of the “Data Validation” sheet.

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

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

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

• Run the DataValidationDropDown subroutine from the Visual Basic window.
• Choose a name from the list.
• The rest of the will be auto-filled with the  VLOOKUP function.

Read More: Excel VBA InputBox with Multiple Lines

Frequently Asked Questions

1. What is the difference between Application InputBox and InputBox in Excel VBA?

The main difference between the InputBox function and the Application.InputBox method is that the Application.InputBox method provides more options, like Type, Left, Right, PasswordChar, etc., to add flexibility in terms of the type of input box and the appearance of the dialog box. These features allow the Application.InputBox method to display a numeric input box or a date input box, or specify the coordinates and size of the input box.

2. What is the difference between MsgBox () and InputBox ()?

MsgBox() is used to show a message to the user and prompt the user for a response, whereas InputBox() is used to ask the user for input and return the value entered by the user.

3. How to insert a drop-down list in Excel with data validation?

To insert a drop-down list in Excel with data validation, follow these steps:

• Select the cell or cells where you want to insert the drop-down list.
• Go to the Data >> Data Validation.
• In the “Settings” tab of the “Data Validation” dialog box, select “List” in the “Allow” dropdown menu.
• In the “Source” field, enter the list of values you want to appear in the drop-down
• Click “OK” to close the “Data Validation” dialog box.

Things to Remember

• ActiveX Controls combo box may have compatibility issues with some versions of Excel or in some security settings.
• While adding drop-down lists to the Combo Box or UserForm ComboBox make sure to exclude duplicate values.
• While adding any data validation list to a cell, make sure to clear any former data validation list ( if available) from that cell. You can run the following command in the macro,

Range("G5").Validation.Delete

Download Practice Workbook

You can download the practice book here.

Conclusion

Today, we discussed 2 ways to get an Excel VBA input box with drop down list. These methods allow users to incorporate a drop-down list in the input box and choose from multiple options.

Related Articles

• Excel VBA: Create InputBox with Multiple Inputs
• How to Use Excel VBA InputBox with Example
• Excel VBA: InputBox with Password Mask
• How to Handle VBA InputBox Cancel Button in Excel