ActiveSheet vs. WorkSheet

Question

I'm using Excel for Mac 2011, and I have a couple Check Boxes in one sheet. I'm trying to automate them with the following code:

Private Sub CheckBox12_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    With ws
        If .Shapes("CheckBox12").OLEFormat.Object.Value = xlOn Then
           .Range("CK1").EntireColumn.Hidden = False
       Else
           .Range("CK1").EntireColumn.Hidden = True
       End If
   End With
End Sub

This code gives me the error: Run-time error 445 Object does not support this action.

However if remove ws and just do

Private Sub CheckBox12_Click()
    With ActiveSheet
         If .Shapes("CheckBox12").OLEFormat.Object.Value = xlOn Then
            .Range("CK1").EntireColumn.Hidden = False
         Else
            .Range("CK1").EntireColumn.Hidden = True
        End If
    End With
End Sub

This works just fine.

What's the deal here? I know I can just use ActiveSheet, but I always like to first set it = ws because it gives the dropdown list of properties/methods as I code.

I tried the checkbox part of your code, and it's working, whether it's activesheet or worksheet. the problem seems to lie elsewhere. Note: I am working on the Windows OS. — hnk, Jul 14 '14 at 15:03
You just declared Dim ws As Worksheet, but did not assigned it to the actual one. Quick answer to your Q: use the second code snippet. Regards, — Alexander Bell, Jul 14 '14 at 15:15
Thanks guys. Alex I say Set ws = ActiveSheet after declaring, is that not enough? — Yunfei, Jul 14 '14 at 15:39
Which line gives the error? Or does it give the error before running the first line of code? — D_Bester, Jul 15 '14 at 14:57
If .Shapes("CheckBox12").OLEFormat.Object.Value = xlOn gives me the error. It seems that if I use ws instead of ActiveSheet, it says OLEFormat.Object doesn't have a .Value property attached to it... but it also seems to magically appear if I change to ActiveSheet. Also no I don't have other workbooks open. — Yunfei, Jul 15 '14 at 15:07

D_Bester · Answer 1 · 2014-07-15T15:30:45.180

I think you're getting a compiler error rather than a run-time error.

I suspect the reason ActiveSheet works is because the compiler doesn't check it. On the other hand, ws doesn't work because the compiler is trying to parse it and actually has a false flag. So the compiler checker has an error. It is flagging an error that actually should not be an error.

Object doesn't support this action (Error 445)

EDIT: Also try this and let me know if it works:

Dim ws As Object
Set ws = ActiveSheet
With ws
    ...

It is also worth noting that ActiveSheet and Worksheet are not the same thing. ActiveSheet can also include a ChartSheet; but a ChartSheet can never be a Worksheet. So maybe it's not surprising that there are differences between With ws and With ActiveSheet.

Another thing you should try is setting your object to a variable:

Dim ws As Worksheet
Set ws = ActiveSheet
Dim chk As Object
With ws
    Set chk = .Shapes("CheckBox12").OLEFormat.Object
    If chk.Value = xlOn Then
        .Range("CK1").EntireColumn.Hidden = False
    Else
        .Range("CK1").EntireColumn.Hidden = True
    End If
End With

This may remove the ws variable from the equation. Of course it would be better to dim as the correct object rather than using the generic as Object but I'm not sure what it would be.

Should I just use On Error Resume Next then..? Seems kinda hand-wavy — Yunfei, Jul 15 '14 at 15:09
No you should not use On Error Resume Next. If ActiveSheet works then I would use that whether or not you understand it. Of course you did pose the question precisely because you want to understand it. — D_Bester, Jul 15 '14 at 15:16

ActiveSheet vs. WorkSheet

1 Answers1

Linked