How to initilize a combobox with VBA?

[iggydarsa]

Hi all,

The spreadsheet has a combobox (Form Control / NOT ActiveX) named ddlCombo.
It has the following values:
Alice
Bob
Charlie

What is the line of code to make the value "Bob" selected in the combobox via VBA.

Thanks.

 

[Domenic]

Try...

Worksheets("Sheet1").DropDowns("Drop Down 1").Value = 2

Or, when the index number is unknown...

Sub test()

    Dim ddlCombo As DropDown
    Dim sFindWhat As String
    Dim vMatchVal As Variant
    
    sFindWhat = "Bob"
    
    Set ddlCombo = Worksheets("Sheet1").DropDowns("Drop Down 1")
    
    vMatchVal = Application.Match(sFindWhat, ddlCombo.List, 0)
    If Not IsError(vMatchVal) Then
        ddlCombo.Value = vMatchVal
    Else
        MsgBox "The item '" & sFindWhat & "' was not found.", vbInformation
    End If
    
End Sub

Change the sheet name and dropdown name, accordingly.

Hope this helps!

 

[iggydarsa]

Thank You!