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"). Again the first step would be to modify the source data: Step 2: In the next step we need to update the drop down list to accommodate for the changes made in its source. The code below must be copied to the sheet with the source data.The highlighted parts must be changed based on the location of your source data and the location you would like the drop down list to appear: Note the highlighted section must changed if your source data is not in Sheet1 starting from cell A1.
Range(Value Range String) 'The range containing the values Dim Create On Sheet As Worksheet Set Create On Sheet = Worksheets(Create On Sheet Name) 'The sheet containing the values Dim Create In Range As Range: Set Create In Range = Create On Sheet. Add Type:=Validation Type, Alert Style:=Validation Alert Style, Operator:=Validation Operator, Formula1:=Validation Formula1, Formula2:=Validation Formula2 .
Add Type:=xl Validate List, Alert Style:=xl Valid Alert Stop, _ Operator:=xl Between, Formula1:=Join(My List, ",") End With Sub DV_Test() Dim Validation List(5) As Variant, i As Integer For i = 0 To UBound(Validation List) Validation List(i) = i 1 Next With Range("A1").
Add Type:=xl Validate List, Alert Style:=xl Valid Alert Stop, _ Operator:=xl Equal, Formula1:=Join(Validation List, ",") .
The resulting string will be something like this: “=Sheet1!
Create a drop down list of items in a cell, with Excel's Data Validation feature.