Reference activeX control through worksheet variable (vba)

Question

I am trying to assign a worksheet to a variable and then work with the controls on that sheet. I am really confused about why referencing the controls on the named variable wont work when I can from the sheet number.

Sub SheetNames()
    Dim i As Integer

    Dim pres As Worksheet
    Call NumSheets

    Set pres = Sheets("Presentation") 'Sheet18 (assignment works)
    Sheet18.lbSheets.Clear 'This works
    pres.lbSheets.Clear 'This fails (method or data member not found)
End Sub

Tim Williams Tim Williams · Accepted Answer · 2017-01-03T08:05:55

A Worksheet object is a generic "template" object type, which has only the "out of the box" properties which apply to all Worksheet objects. Your specific sheet Sheet18 has controls added, and those controls are now parts of the sheet's object model, but they are not part of the generic Worksheet class: your sheet is now more like a specific "subclass" of Worksheet, so you cannot declare it as Worksheet and still access the controls via that reference.

However, you could declare it As Sheet18 (the "subclass" for that specific sheet):

Sub Tester()

    Dim sht As Worksheet
    Dim sht2 As Sheet18

    Set sht = Sheet18
    Debug.Print sht.lbSheets.ListCount '<< nope - compile error because the
                                               '    Worksheet class has no
                                               '    "lbSheets" member

    Set sht2 = Sheet18
    Debug.Print sht2.lbSheets.ListCount '<< OK!
    'but you may as well skip the variable and just use
    Debug.Print Sheet18.lbSheets.ListCount

    'this also works if you know the sheet name and the control name
    Set sht = ThisWorkbook.Sheets("Presentation")
    Debug.Print sht.OLEObjects("lbSheets").Object.ListCount

End Sub

Reference activeX control through worksheet variable (vba)

1 Answers