Search code examples
excelactivexobjectvba

How to reference ActiveX ComboBox in Excel 2016 VBA


I've tried various solutions on here and elsewhere such as:

dim wb as workbook, ws as worksheet

Dim cb As Object
Set cb = ws.OLEObjects("ComboBoxViews")

or

ComboBoxViews

or

ws.comboboxviews

But all return Error 1004, the item with specified name wasn't found. But it DOES exist, checking properties the name is very clearly 'comboboxviews'.

Any ideas?

EDIT:

To make it clear for anyone else seeking help in the future and using Romcel's very helpful code as a base, it seems that in order to add items to an ActiveX ComboBox (which is my final goal), you need to reference it as an object:

Sub caller3()
Dim ws As Worksheet
Dim oleob As OLEObject
Set ws = ThisWorkbook.Sheets("Sheet1")

For Each oleob In ws.OLEObjects
  If TypeName(oleob.Object) = "ComboBox" Then
    oleOb.Object.AddItem "TEST"  <<< CORRECT
    oleOb.AddItem "TEST"  <<< INCORRECT
  End If
Next

End Sub

Solution

  • You can bluntly refer to your controls in the worksheet like.

    Sub caller1a()
    Sheet1.ComboBox1.Value = "value 1a"    '   no errors
    End Sub
    


    But you cannot referto them like this.

    Sub caller1b()
    Dim ws As Worksheet
      Set ws = Sheet1
      ws.ComboBox1.Value = "value1b"    '  will give error
    End Sub
    


    If you are familiar with how Excel gives each control their respective index. You can refer to each control as.

    Sub caller2()
    Dim ws As Worksheet
      Set ws = Sheet1
      ws.OLEObjects(1).Object.Value = "value2"
    End Sub
    

    Or just safely loop through them and check if it is the right control you are working with.

    Sub caller3()
    Dim ws As Worksheet
    Dim oleob As OLEObject
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    For Each oleob In ws.OLEObjects
      If TypeName(oleob.Object) = "ComboBox" Then
        oleob.Object.Value = "value3"
      End If
    Next
    
    End Sub
    

    Hope this helps. Good luck!