"Method or item not found" when Dim'ing worksheet is a sheet but not an option
Let's take a look at this simple example. On a new sheet create an ActiveX checkbox named Checkbox1
Try the following two routines. The first does not compile with the error "Method or data not found", the second 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
+3
source to share
1 answer
The problem is with the line ws.CheckBox1.Value
. You cannot use it like that and hence you get this error. try it
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, 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
+4
source to share