VBA ActiveX Combobox reference

[Damian86]

Hi guys!

I can't change/read any of my activeX combobox properties without getting an error. I can address and modify my combobox via this code:

ActiveSheet.Shapes.Range(Array("My_Combobox_Name")).Select
With Selection
.ListFillRange = "MySheet!" & MyRange.Address
.LinkedCell = "A1"
.Object.BackColor = &HC0C0C0
.Placement = xlFreeFloating
End With

As you can see, I change 4 properties with this code: ListFillRange from another sheet, linked cell address, background color and make it free float.

The problem is, I want to refer to the combobox directly, without having to activate the sheet with it and then activating the combobox itself. Is there a way? I tried various combinations with using its name but I cannot get it to work. I've read different solutions but I don't think they apply to my problem since they refer to OLEObjects.

Thank you for oyur help!

 

[Andrew Poulsom]

Instead of:

With Selection

you can use eg:

With Worksheets("Sheet1").OLEObjects("My_Combobox_Name")

Change the worksheet reference to suit.

 

[RoryA]

An ActiveX combobox is contained in an OLEObject if it's on a worksheet. Try:

With Sheets("Sheet name").OLEObjects("My_Combobox_Name")
.ListFillRange = "MySheet!" & MyRange.Address
.LinkedCell = "A1"
.Object.BackColor = &HC0C0C0
.Placement = xlFreeFloating
End With

 

[Damian86]

Thank you guys! It worked well.