How to Create Dropdown List in Excel?
Data validation list is a list of predefined values that users can pick from Dropdown list.
How do we add it to a Excel Sheet? – Manually & Programmatically
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 be allowed to type only the values 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 drop down 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.
Instead of adding values in range to a validation list, what if you need to add list from a variable string? Just change the AddrRange.Range with a string variable as in below example.123'ListValue = "Value1, Value2, Value3, Value4.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="=" & ListValues
Isn’t this interesting?
VBA Change Selection in Data Validation List
This does not require any special code. It can be done just by changing the value in the particular cell that has a validation list. Here is an example:123456'Considering cell A1 has a validation of these Values'List Value1'List Value2'List Value3'Here is how to change the selectionThisworkbook.Range("A1") = "List Value2"
We had added values to list, changed the selection. What else is there to learn?
Read Data Validation List Values – Using VBA
We are programming with a Excel sheet & there is a validation list. But, we could see only the value that is currently selected. How do we read all the values in a validation list apart from what is currently selected?
Use this code to do this.12345Sub Get_Validation_List()Dim ListStr as StringListStr = Thisworkbook.Range("A1").Validation.Formula1MsgBox ListStrEnd Sub
Well now it looks like a complete package on Validation list. No Wait. There is one more piece that might come handy.
Delete Validation List in Worksheet
If you know the cell address that you are going to delete, then it is easy. But there are times, where we might want to delete all the Validation lists in a Sheet or a Excel Workbook.
Here is the code to do this. Just so much easy – isnt it?12345678Sub Delete_Validation_List()'Delete Drop Down in a RangeThisworkbook.Range("A1").Validation.Delete'Delete All Validation Lists in a SheetThisworkbook.Cells.Validation.DeleteEnd Sub
Yes. It does not need to be done with Loop each Dropdown. One line of Code does it all.
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?
Excel VBA Macro – Data Validation – Drop Down List