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

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