ActiveSheet与WorkSheet [英] ActiveSheet vs. WorkSheet

问题描述

我正在使用Excel for Mac 2011，我有一个复选框在一张。我试图使用以下代码自动化：

  Private Sub CheckBox12_Click（）
 Dim ws As Worksheet 
设置ws = ActiveSheet 
与ws 
如果.Shapes（CheckBox12）。OLEFormat.Object.Value = xlOn然后
 .Range（CK1）。EntireColumn.Hidden = False 
 Else 
 .Range（CK1）。EntireColumn.Hidden = True 
 End If 
 End with 
 End Sub 
  

此代码给我错误：
运行时错误445对象不支持此操作。

但是，如果删除ws而只是做

  Private Sub CheckBox12_Click（）
 With ActiveSheet 
如果.Shapes（CheckBox12）。OLEFormat.Object.Value = xlOn然后
 .Range（CK1）。EntireColumn.Hidden = False 
 Else 
 .Range CK1）。EntireColumn.Hidden = True 
 End If 
 End With 
 End Sub 
  

这工作很好。

这里有什么交易？我知道我可以使用ActiveSheet，但是我总是喜欢首先设置它= ws，因为它给出了我的代码的属性/方法的下拉列表。

解决方案我认为你得到一个编译器错误，而不是运行时错误。

我怀疑ActiveSheet工作原因是因为编译器没有不要检查另一方面，ws不起作用，因为编译器试图解析它，实际上有一个false标志。所以编译器检查器有错误。它标出一个实际上不应该是错误的错误。

对象不支持此操作（错误445）

编辑：还尝试这个让我知道如果它有效：

  Dim ws As Object 
设置ws = ActiveSheet 
 With ws 
 ... 
  

还值得注意的是， ActiveSheet 和工作表不一样。 ActiveSheet 还可以包括ChartSheet;但ChartSheet不能是工作表。所以也许并不奇怪，与ws 和之间存在差异。使用ActiveSheet 。

另一件你应该尝试的是将对象设置为一个变量：

  Dim ws As Worksheet 
设置ws = ActiveSheet 
 Dim chk As Object 
用ws 
设置chk = .Shapes（CheckBox12）。OLEFormat.Object 
如果chk.Value = xlOn Then 
 .Range（CK1）。EntireColumn.Hidden = False 
 Else 
 .Range（CK1）。EntireColumn.Hidden = True 
 End If 
 End With 
  

这可以从等式中删除ws变量。当然，最好将其作为正确的对象变暗，而不是使用通用的作为Object ，但我不知道会是什么。

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 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.

这篇关于ActiveSheet与WorkSheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！