1. How do I Insert a 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.
Now, you can see a drop down box in the selected worksheet cell.
If you click on the on the drop down list, the values you entered or data from source will be listed one by one. Users are allowed to only enter any of the values in the list.
If they type any other value, then Excel will throw an error message asking to type valid value.
Let’s see how to create this drop down list in Excel using VBA code.
2. Excel VBA – Create Drop down list – Data Validation
Let’s see how to get an Excel drop down 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.How to Add a Drop Down list in Excel - using VBA Code - Srouce As RangeVisual Basic1234567891011121314151617'Adding a Data Validation Excel Drop Down List using VBAPublic Function Add_Drop_Down_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 or Drop down 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 or Drop Down 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. How to Remove DropDown box in Excel & VBA?
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_Drop_Down_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.
Manually Remove DropDown list in a Cell:
- Select the cell that has drop down list.
- Choose Menu -> Data -> Data Validation
- In Settings Tab, choose “Any Value” for Allow field.
- Click Ok.
You can now see that the drop down symbol is removed in the selected cell.
5. VBA Change Selection: Update Drop Down List in Excel
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 Drop down 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 list box menu in Excel?
Data validation drop down list in Excel, is an array of predefined values that users can pick.
In a cell, if you want the user to only 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 or drop down 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 drop down list or Data validation 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?
How to Create a Drop down list in Excel? – A Complete Guide