Validating drop down list
Dim xl Validate List(6) As Integer xl Validate List(1) = 1 xl Validate List(2) = 2 xl Validate List(3) = 3 xl Validate List(4) = 4 xl Validate List(5) = 5 xl Validate List(6) = 6 With Range("A1"). Add Type:=xl Validate List, Alert Style:=xl Valid Alert Stop, Operator:= _ xl Between, Formula1:=Validation List . Show Error = True End With Dim My List(5) As String My List(0) = 1 My List(1) = 2 My List(2) = 3 My List(3) = 4 My List(4) = 5 My List(5) = 6 With Range("A1"). Show Error = True End With End Sub 'Simple helper procedure to create a dropdown in a cell based on a list of values in a range 'Value Sheet Name : the name of the sheet containing the value range 'Value Range String : the range on the sheet with name Value Sheet Name containing the values for the dropdown 'Create On Sheet Name : the name of the sheet where the dropdown needs to be created 'Create In Range String : the range where the dropdown needs to be created 'Field Name As String : a name of the dropdown, will be used in the input Message and Error Message 'See example below Example Create Drop Down Public Sub Create Drop Down(Value Sheet Name As String, Value Range String As String, Create On Sheet Name As String, Create In Range String As String, Field Name As String) Dim Value Sheet As Worksheet Set Value Sheet = Worksheets(Value Sheet Name) 'The sheet containing the values Dim Value Range As Range: Set Value Range = Value Sheet.
If the drop down list (data validation) and the data are in the same sheet you would reference them using a statement like “=A1: A6”.To create a named range, select the cells that will be on the list, and name the selection.Here's how: To keep the drop-down list up to date with changes in your data, change the choices in the list.Note you must change the highlighted parts based on the location of your source and the location for your drop down list: In order to modify, add, insert and remove items from a drop down list created using data validation, you would have to follow 2 steps.Step 1: The first thing you would have to do is change the source data.