1. How to Create Drop down List in Excel?
To add drop down data validation list to Excel Sheet, the steps are:
- Select a cell to create drop down list.
- Click on ‘Data’ option from 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.
Let’s see how to create this in Excel using VBA code.
2. Excel VBA – 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.
Note: Don’t execute this code with F5. Go to any sheet. Follow the steps explained below the code.Add Excel Validation Data Drop Down list using RangeVisual Basic123456789101112131415161718'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.
2.a How to Add Validation list using a variable field?
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.Add Variable List to Validation ListVisual Basic123'ListValue = "Value1, Value2, Value3, Value4.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="=" & ListValues
Isn’t this so easy than to mention the range address?
3. Read Data Validation List Values – Using VBA
There is a validation list already. We could see the value that is currently selected.
We are programming with a Excel sheet. How do we read all the values in a validation list apart from what is currently selected?
Use this code to do this.Read Validation List Values into a VariableVisual Basic12345Sub 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.
4. 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?Delete Drop Down Lists in ExcelVisual Basic12345678Sub 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.
5. 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:VBA Change Validation List SelectionVisual Basic123456'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?
What is a Drop down menu in Excel?
Data validation drop down list is a array of predefined values that users can pick.
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.
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.
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