Set value of combobox with VBA

Question

I have a combo box that contains 200+ values from a named range and the user can choose to either choose any of these 200+ values or freely type in any other value into the combo box. This works fine.

My problem is that I am unable to fill in the same combo box with a value specified in a string variable in my VBA code. I want to do the following:

Sub FillInComboBox()
Dim strExample as String

strExample = "Random Text"

Worksheets("Sheet1").Shapes("ComboBox1").Value = strExample 

End Sub

I get the "Run-time error '438: Object doesn't support this property or method". I have also tried a lot of variations of the code above and I have googled for two hours without success, so I now turn to you as my last hope to get this working.

Answer 1

Use following sub.

    Sub FillInComboBox()
    Dim strExample As String
        strExample = "Random Text"

        With Sheet1.ComboBox1
            .AddItem strExample
            .AddItem "Second Item"
            .AddItem "Third Item"
        End With
    End Sub

Answer 2

I solved it myself:

Worksheets("Sheet1").OLEObjects("ComboBox1").Object.Value = strExample

Answer 3

Looks like you're using an ActiveX ComboBox. There are a couple ways to accomplish what you want.

1. Set the LinkedCell property of the ComboBox to your desired location (even on a hidden worksheet).

   • You can read the value of the combobox from that cell, even manually entered text
     Worksheets("Sheet1").Shapes("ComboBox1").LinkedCell = "C1"
   • You can set the value of the cell and it will be reflected in the combobox
     boxValue = Range("C1")
Range("C1") = "custom entry"

2. Access the ActiveX control object directly.

   • I emphasized "object" because the control is a child object of the worksheet object. So you can do this:
     msgbox Worksheets("Sheet1").ComboBox1.Value
     and it gets you the value directly. Remember to use the name of the control you set in the Name property of the control.
   • Setting the value works the same way:
     Worksheets("Sheet1").ComboBox1.Value = strExample

Either way will gain access to the control data.