Filling an embedded comobox (ActiveX) on a spreadsheet with a range

[RawlinsCross]

Having a bit of an issue with something I thought would be easier (isn't that always how it is). Anyhow, I have spreadsheet with embedded ActiveX comoboxes and I want to fill each with a given range. I have a function that returns the appropriate range (I may have to convert this to a string address???) but I can't even step the code into this function as it fails before hand with the error "Object doesn't support this property". Tried researching but I think I'm getting confused with answers that are based on FORM controls rather than ACTIVEX controls.

Public Sub InitializeControls()

Dim wSht As Worksheet
Dim ctrl As OLEObject

Set wSht = ThisWorkbook.Worksheets("Form")

For Each ctrl In wSht.OLEObjects
    If ctrl.progID = "Forms.ComboBox.1" Then
        ctrl.ListFillRange = GetControlRange(ctrl.Object.Name)   '<----- Fails here  (also do I need convert this to a string address?)
    End If
Next ctrl

End Sub

 

[RoryA]

You need the ctrl.Object and yes, you need the address as a string.

 

[RawlinsCross]

That's the kicker, cause I tried it with and without ".Object". The line below as fails with the exact same error.

ctrl.Object.ListFillRange = GetControlRange(ctrl.Object.Name)

 

[RawlinsCross]

Figure it out... I was so focused on the ".ListFillRange" that I didn't realize that I was failing on the ctrl.Object.Name. The code below works.

Public Sub InitializeControls()

Dim wSht As Worksheet
Dim ctrl As OLEObject
Set wSht = ThisWorkbook.Worksheets("Form")

For Each ctrl In wSht.OLEObjects
    If ctrl.progID = "Forms.ComboBox.1" Then
        ctrl.ListFillRange = GetControlRange(ctrl.Name).Address  'ctrl.Object.ListFillRange does not work
    End If
Next ctrl

End Sub