How to Create Dropdown List in Excel?
It is a list of predefined values that users can pick from Dropdown list.
In a cell, if you want the user to pick values like “Yes or No” or any such list of values, then we go for this Drop down Data Validation lists in Excel. In this cell, users will not be allowed to type any values other than that are already mentioned in the validation list.
Let’s see how to create this in Excel manually and through VBA.
How to create drop Down menu in Excel?
To create a drop down list in Excel, follow these steps.
- Select a cell to create drop down list.
- Click on ‘Data’ option in Menu.
- Choose ‘Data Validation’ option in Data Tools tab.
- Select ‘List’ under Validation Criteria ‘Allow’ field.
- Enter values in ‘Source Field’
- Enter delimited list as value1, value2, value3 or
- Choose the Cell Range that has the list for drop down values.
- Click Ok.
Now, you can see a drop down button in the Excel worksheet cell that we created a validation list. Excel will give a warning message if we try to enter a value that is not present in the Drop down list.
Note: The above steps explain adding validation list to a single cell. But, this can also be applied to range of cells. Instead of choosing one cell, select range of cell and then follow from step2.
Excel VBA to create Data validation Drop down list
Let’s see how to get an Excel dropdown validation list using VBA Macro. Just follow these steps to create a dynamic list in your Excel.
- Open a new Excel workbook.
- Press Alt + F11 to view VB Editor for Excel.
- Click on Insert in Menu and choose Module.
- This will insert a new module in current project.
- Copy paste the below code.
Don’t execute this code with F5. Go to any sheet. Follow the steps explained below the code.
123456789101112131415161718'Adding a Data Validation Excel Drop Down List using VBA'OfficeTricks.comPublic Function Add_List(AddrRange As Range) As StringWith Selection.Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="=" & AddrRange.Address.IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".ShowInput = True.ShowError = TrueEnd WithAdd_List = AddrRange(1)End Function
Enter these values in Column A. Value1 in A1, Value2 in A2, Value3 in A3. Then in Cell B1 enter “=Add_List(A1:A3)” and press Enter.
Now, you can see B1 has a drop down list added with default value as first entry in list. Yes. We have added a new formula to Excel that can add a Validation list on the Go. Isn’t this interesting?
We have used the Selection.Validation option in Excel Macro to create this dropdown list. In the .Add property, we have many fields that can be used to create different type of validation on a cell. i.e., we can make the Excel to validate a input entry made on any cell to match the criteria.
Previous Post: get Excel VBA Shell Command Output within Macro
Next Post: Activate WhatsApp Video Call – What Exactly You Need To Do?
How to Get Data Validation Excel Drop down List?