CODEWITHSUNDEEP
excelactivexobjectvba
aSystemOverload
aSystemOverload

Reputation: 3074

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

Upvotes: 2

Views: 6684

Answers (1)

Romcel Geluz
Romcel Geluz

Reputation: 603

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!

Upvotes: 3

Related Questions