Accessing ActiveX control from Module

Question

I guess I have my basic wrong here, can anybody please correct me?

I have an ActiveX control in "home" worksheet. Now, when I code,

This works:

MsgBox (ThisWorkbook.Worksheets("home").quantity.Value)

So does this:

With ThisWorkbook.Worksheets("home")
    MsgBox (.quantity.Value)
End With

But this does not:

Dim wh As Worksheet
Set wh = ThisWorkbook.Worksheets("home")
MsgBox (wh.quantity.Value)

Can anybody please explain the difference?

Rory Rory · Accepted Answer · 2014-11-19T12:04:23

The Worksheet class does not have a quantity member so the compiler will object to it. You either need to use an Object variable (as in both of your prior examples), or access the control using the OLEobjects property of the worksheet:

MsgBox wh.OLEObjects("quantity").Object.Value

for example.

Accessing ActiveX control from Module

1 Answers