ActiveSheet and WorkSheet
I am using Excel for Mac 2011 and I have a couple of Check Boxes on the same sheet. I am 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: Runtime Error 445 Object does not support this action.
However if you 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 great.
What's the deal here? I know I can just use ActiveSheet, but I always like setting it to = ws first because it gives a dropdown of properties / methods when coding.
source to share
I think you are getting a compiler error, not a runtime error.
I suspect the reason the 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 in fact has a fake flag. So the compiler check has an error. It posts an error that shouldn't actually be an error.
Object does not support this action (Error 445)
EDIT: 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
may also include ChartSheet; but ChartSheet can never be a sheet. Therefore, it may not be 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 can remove the ws variable from the equation. Surely it would be better to flatten as the correct object rather than use a generic one as Object
, but I'm not sure what that would be.
source to share