The Data Validation dialog box shrinks down to just the Source box and you can access your workbook under the dialog box.Click the tab for the worksheet containing the drop-down list options. The worksheet name and the cell range with the options is added to the Source box on the Data Validation dialog box.
Earlier in this article, we created a list of items on a separate worksheet. 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, ",") . Select the cells you want to add the drop-down lists too. For our example, select List from the Allow drop-down list to create a drop-down list in each of the selected cells.Now, we need to specify the source for the options in each drop-down list. The first method involves manually typing the options in the Source box separated by commas.
Range(Value Range String) Dim Create On Sheet As Worksheet Set Create On Sheet = Worksheets(Create On Sheet Name) Dim Create In Range As Range: Set Create In Range = Create On Sheet.