VBA to select a drop down list value

Question

I have taken over a spreadsheet, for a company, that does various calculations based on inputs including a lot of dropdown (ComboBox) lists.

I am try to write a VBA script to automatically choose another value from the list (for the purposes of quickly testing the spreadsheet).

I have tried the following code but it does not seem to work

ActiveSheet.Shapes("Dropdown3").Select
With Selection
Sheets("SheetName").Shapes("ListName").ControlFormat.ListIndex = 0
End With

Can you help? What further information would be useful?

Loop through the list of possible values instead. Side note: in general, you want to [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Apr 11 '23 at 14:36
"does not seem to work" - what does it do or not do which is different from what you expect? Setting `ListIndex` to 0 will de-select any already-selected value. — Tim Williams, Apr 11 '23 at 15:23

score 0 · Answer 1 · answered Apr 12 '23 at 03:35

Sub ComboBoxValues()
    Dim cbo As ComboBox
    Dim cbValue As String
    'Change Sheet1 and ComboBox1 to your sheet and control name
    Set cbo = Sheet1.ComboBox1
        
    Dim i As Long
    For i = 0 To cbo.ListCount - 1
        cbValue = cbo.List(i)
        cbo.Value = cbValue
        Application.Wait Now + TimeValue("0:00:02")
    Next i
End Sub

VBA to select a drop down list value

1 Answers1