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

Question

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.

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.

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.

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.

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.

• 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

Here is my code attached to 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...

Option Explicit

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

---

Update 2012-10-09

Here is a simple way to replicate this bug in 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

Solution

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.

As a workaround, I moved your Sub CommandButtonX_Click to Module 1, removed the Private keyword, 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.

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

Hope that helps ...

Comments

[Dave Lewis]

Thank you for the response. Regarding your 1st point, all of the buttons don't lock up. The last button clicked still works. The rest don't. So I can't see this as a scope issue since all the code is declared the same. Furthermore, Excel 2007 works fine without this problem.

[Dave Lewis]

I'm thinking of a work around where I toggle the function of the button between selecting a group of sheets and deselecting them. Since the one button still works, this may do the trick.

[MikeD]

I experienced that once more than 1 sheet are selected, none of the buttons worked.