3

Consider this simple example. In a new sheet create a ActiveX Checkbox called Checkbox1

Try the following two subroutines. The first does not compile with a "Method or Data Member Not Found" error, the second one works fine.

Why doesn't the first example work?

Option Explicit

Sub DoesntWork()
Dim ws As Worksheet
Set ws = Worksheets(1)

MsgBox "Checkbox state is: " + CStr(ws.CheckBox1.Value)

End Sub

Sub Works()
Dim ws As Variant
Set ws = Worksheets(1)

MsgBox "Checkbox state is: " + CStr(ws.CheckBox1.Value)

End Sub
pedram
  • 2,931
  • 3
  • 27
  • 43

1 Answers1

4

The problem is with the line ws.CheckBox1.Value. You can't use it like this and hence you are getting that error. Try this

Sub Sample()
    Dim ws As Worksheet
    Dim objole As OLEObject

    Set ws = Worksheets(1)

    Set objole = ws.OLEObjects("CheckBox1")

    MsgBox "Checkbox state is: " & objole.Object.Value
End Sub

If you want to use the Object directly then you can also use this

Sub Sample()
    Dim ws As Worksheet

    Set ws = Worksheets(1)

    MsgBox "Checkbox state is: " & Worksheets(ws.Name).CheckBox1.Value
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • That's weird. Why wouldn't it I be able to access it through ws.Checkbox1 but it works fine through Worksheets(ws.Name).Checkbox1? – pedram Aug 05 '15 at 19:10
  • I can give you the answer but I want you to find it yourself ;). Do this. In your `Sub Works()`, Put a `Watch` on the the object `ws` and then see what is the type of `ws` during runtime. – Siddharth Rout Aug 05 '15 at 19:15
  • 1
    You may want to see [THIS](http://stackoverflow.com/questions/21386768/why-am-i-having-issues-assigning-a-range-to-an-array-of-variants/21411922#21411922) on how to add a `Watch` – Siddharth Rout Aug 05 '15 at 19:20
  • I was following the locals window which did show the difference between the two. So I can see that `Dim ws As Worksheet` gives me a `Worksheet/Sheet1 type`. And `Dim wsObj as Object` gives me a `Object/Sheet1` type. But when I expand both `ws` and `wsObj` in the Locals Window, they both list `CheckBox1` as a property...so it must have something to do with forcing a worksheet into a `Worksheet` type vs. `Object`, but I'm still missing something with Excel's object model... – pedram Aug 05 '15 at 19:38
  • 1
    `Checkbox1` shows only for Variant/Object/Worksheets/Sheet1(in this case) and not for `Worksheet`. If you use intellisense, you will not get the `.Checkbox1` option when you declare ws as worksheet – Siddharth Rout Aug 05 '15 at 19:54