[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.