Excel VBA 2010 - 命令按钮停止使用选定的多个工作表

Excel VBA 2010 - Command buttons stop working with multiple sheets selected

提问by Dave Lewis

My problem are command buttons that fail to respond when I've selected multiple sheets in a workbook. The workbook I'm testing here is meant only to study and troubleshoot this problem, which I originally found in a much more complicated workbook.

我的问题是当我在工作簿中选择多个工作表时，命令按钮无法响应。我在这里测试的工作簿仅用于研究和解决这个问题，我最初是在一个更复杂的工作簿中发现的。

My workbook has a total of 5 worksheets. There are four ActiveX command buttons on sheet1. These four buttons launches code to either select several worksheets together or just sheet1.

我的工作簿共有 5 个工作表。sheet1 上有四个 ActiveX 命令按钮。这四个按钮启动代码以选择一起选择多个工作表或仅选择 sheet1。

After selecting multiple sheet together, only the last button clicked actually can be clicked again, the rest of the buttons on the sheet1 don't respond anymore, like they're disabled or something. Once I manually deselect the sheets so that just sheet1 is selected, the controls start working normally.

一起选择多个工作表后，实际上只有最后点击的按钮可以再次点击，工作表1上的其余按钮不再响应，就像它们被禁用之类的一样。一旦我手动取消选择工作表以便只选择工作表 1，控件就会开始正常工作。

Weird.. I think it must be some sort of bug in Excel. I also can replicate the problem if I manually select multiple worksheets rather than let the code do it.

奇怪.. 我认为这一定是 Excel 中的某种错误。如果我手动选择多个工作表而不是让代码来做，我也可以复制这个问题。

Some of my findings so far...

到目前为止，我的一些发现...

1. It doesn't seem to matter how many sheets I select so long as its two or more.
2. VBA code selection or manual SHIFT-CLICK doesn't matter.
3. The last button activate still runs once the other buttons gets locked up.
4. I only get this with Excel 2010, Excel 2007 didn't have this problem.
5. I've replicated the problem in a isolated workbook, so I don't think this is corruption issue.

1. 只要是两张或更多张，我选择多少张似乎并不重要。
2. VBA 代码选择或手动 SHIFT-单击无关紧要。
3. 一旦其他按钮被锁定，最后一个按钮激活仍然运行。
4. 我只在 Excel 2010 中得到这个，Excel 2007 没有这个问题。
5. 我已经在一个孤立的工作簿中复制了这个问题，所以我认为这不是腐败问题。

The 4 command buttons execute the functions shown below. Each button marks adjacent cells if the code runs. I put a 1 second delay to verify clicking a button twice in a row was working.

4 个命令按钮执行如下所示的功能。如果代码运行，每个按钮都会标记相邻的单元格。我延迟了 1 秒来验证连续两次单击按钮是否有效。

• CMD 1: Select sheet1 only
• CMD 2: Select sheet1 only
• CMD 3: Select sheet1 and sheet2
• CMD 4: Select sheet1 through sheet4 via sub routine in module1

• CMD 1：仅选择 sheet1
• CMD 2：仅选择 sheet1
• CMD 3：选择 sheet1 和 sheet2
• CMD 4：通过module1中的子程序选择sheet1到sheet4

Here is my code attached to sheet1....

这是我附在 sheet1 上的代码....

Option Explicit

Private Sub CommandButton1_Click()
    Call MarkCmdsAsInactive
    Me.Select
    Call WaitSeconds(1)
    Range("E6").Value = "CMD 1 Works"
End Sub

Private Sub CommandButton2_Click()
    Call MarkCmdsAsInactive
    Me.Select
    Call WaitSeconds(1)
    Range("E10").Value = "CMD 2 Works"
End Sub

Private Sub CommandButton3_Click()
    Call MarkCmdsAsInactive
    Sheets(Array("Sheet1", "Sheet2")).Select
    Call WaitSeconds(1)
    Range("E14").Value = "CMD 3 Works"
End Sub

Private Sub CommandButton4_Click()
    Call MarkCmdsAsInactive
    Call SelectSomeSheets
    Call WaitSeconds(1)
    Range("E18").Value = "CMD 4 Works"
End Sub


Private Sub MarkCmdsAsInactive()
    Range("E6").Value = "Inactive"
    Range("E10").Value = "Inactive"
    Range("E14").Value = "Inactive"
    Range("E18").Value = "Inactive"
End Sub


Private Sub WaitSeconds(waitInSeconds As Variant)
    Dim newHour As Variant
    Dim newMinute As Variant
    Dim newSecond As Variant
    Dim waitTime As Variant

    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + waitInSeconds
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
End Sub

In module1 I have...

在模块 1 中，我有...

Option Explicit

Sub SelectSomeSheets()
    Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select
End Sub

---

---

Update 2012-10-09

更新 2012-10-09

Here is a simple way to replicate this bug in Excel 2010...

这是在 Excel 2010 中复制此错误的简单方法...

1. Place 4 command buttons on sheet1.
2. Copy the code below to sheet1.
3. Put breakpoints on each of the "End Sub" statements.
4. Try clicking the buttons with just sheet1 selected. All buttons launch routines.
5. SHIFT-CLICK to select a group of sheets.

6. Try buttons again with the sheet group selected. Only the last used button works.

   Private Sub CommandButton1_Click() End Sub

   Private Sub CommandButton2_Click() End Sub

   Private Sub CommandButton3_Click() End Sub

   Private Sub CommandButton4_Click() End Sub

1. 在 sheet1 上放置 4 个命令按钮。
2. 将下面的代码复制到sheet1。
3. 在每个“End Sub”语句上放置断点。
4. 尝试单击仅选择 sheet1 的按钮。所有按钮启动例程。
5. 按住 SHIFT 单击以选择一组工作表。

6. 选择工作表组后再次尝试按钮。只有最后使用的按钮有效。

   Private Sub CommandButton1_Click() End Sub

   Private Sub CommandButton2_Click() End Sub

   Private Sub CommandButton3_Click() End Sub

   Private Sub CommandButton4_Click() End Sub

回答by MikeD

I guess it has to do with the "scope" of the ActiveX buttons (i.e. scope is sheet1 and not sheet1+sheet2+...). It seems that the selection of multiple sheets deactivates command buttons in sheet 1, even if sheet 1 remains the "active" sheet. because the ActiveX components are private to sheet1.

我想这与 ActiveX 按钮的“范围”有关（即范围是 sheet1 而不是 sheet1+sheet2+...）。似乎选择多个工作表会停用工作表 1 中的命令按钮，即使工作表 1 仍然是“活动”工作表。因为 ActiveX 组件是 sheet1 私有的。

As a workaround, I moved your Sub CommandButtonX_Clickto Module 1, removed the Privatekeyword, created a custom ribbon MyTools with the 4 Sub's as selectable elements. This way I moved the visibility of the sub's from sheet level to application level and all works.

作为一种解决方法，我将您Sub CommandButtonX_Click移至模块 1，删除了Private关键字，创建了一个自定义功能区 MyTools，其中 4 个 Sub 作为可选元素。通过这种方式，我将 sub 的可见性从工作表级别移动到应用程序级别和所有工作。

Of course I had also to change Me.Selectinto Sheets("Sheet1").Select(allthough I hate hard codings like this ....)

当然我也不得不Me.Select改成Sheets("Sheet1").Select（虽然我讨厌这样的硬编码......）

Hope that helps ...

希望有帮助...