[VBA] Read Values in Data Validation Drop Down List

[VBA] Read Drop Down List Values

Add a data validation drop down list as explained in this page. Skip this step if there is already a validation created in the Worksheet & You just want to read the items in the list.

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.

Sub readItemsInDropDown()
    Dim sListStr As String
    Dim vListStr
    Dim vListItem
    
    'Read Items in DropDown List
    sListStr = ThisWorkbook.Sheets(1).Range("A1").Validation.Formula1
    vListStr = VBA.Split(sListStr, ",")
    Debug.Print sListStr
    
    'Print Each Item one by one
    For Each vListItem In vListStr
        Debug.Print VBA.Trim(vListItem)
    Next
End Sub

For example, if the worksheet range referred in above code has dropdown with value “Value1, Value2, Value3”. Then executing the above code will give output as given below:

value1, value2, value3
value1
value2
value3

Thus, you can get the validation list value into a string. Or it can be split with delimiter as comma. This way, it can be made to read each value in the list.