CODEWITHSUNDEEP
vbaexcel
Micah Lindstrom
Micah Lindstrom

Reputation: 365

"Worksheet.Visible = xlSheetVisible" Changes the Active Tab

Why is Excel changing the active sheet with my code below, and how can I (elegantly/properly) stop it from doing so?

I have a workbook with many tabs and am using VBA to filter the visible sheets based on my selection(s) in a listbox. Pausing the program after it shows a hidden sheet, using either a breakpoint or Application.Wait (Now), makes everything run right, but this strikes me as bad practice and would prefer an alternative. I cannot reproduce the error in a new workbook with many blank tabs, and removing the Application.ScreenUpdating call at the start and finish does not change my results either, so I assume it's taking Excel too long to process each of my sheets and this is causing this strange error.

This bug is annoying because if I accidentally choose the wrong item in the listbox, Excel switches to another sheet and I have to manually click back to the sheet which contains that listbox to fix my selection.

Application.ScreenUpdating = False
For Each mySheet In Worksheets
    Select Case mySheet.Name
        Case "Cost Overview", "Project Eng.", "Mfg Development", "Labor Summary", "S.O.P.", "MFGD Time", "MFGD Group", "HowTo", ChrW(&H21BB)
            'These sheets are always visible, so do nothing
        Case Else
            mySheet.Visible = xlSheetVisible 'or xlSheetHidden or xlSheetVeryHidden or xlSheetVisible
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            Application.Wait (Now) 'WITHOUT THIS SLIGHT DELAY, ACTIVE TAB CHANGES
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    End Select

Next
For i = 1 To Worksheets("Labor Summary").ListBoxes("ListBoxForSlicers").ListCount

    'The category names are "Div 1" and "Div 2", referenced here and in the ListBox from ColorIndexTable'
    CategoryName = Application.WorksheetFunction.index(Range("ColorIndexTable[Category]"), i)
    CategorySelected = Worksheets("Labor Summary").ListBoxes("ListBoxForSlicers").Selected(i)

    If CategorySelected Then
        Select Case CategoryName
            Case "Div 1"
                For Each mySheet In Worksheets
                    Select Case mySheet.Name
                        Case "Cost Overview", "Project Eng.", "Mfg Development", "Labor Summary", "S.O.P.", "MFGD Time", "MFGD Group", "HowTo", ChrW(&H21BB)
                            'These sheets are always visible, so do nothing
                        Case "MFGC", "CUT", "ASSM", "HOT MELT", "POT", "MOLD", "POST-OP", "MARK", "PACK", "PPRNT", "TEST"
                            'These sheets ought to be visible, so do nothing
                        Case Else
                            mySheet.Visible = xlSheetHidden
                    End Select
                Next

            Case "Div 2"
                For Each mySheet In Worksheets
                    Select Case mySheet.Name
                        Case "Cost Overview", "Project Eng.", "Mfg Development", "Labor Summary", "S.O.P.", "MFGD Time", "MFGD Group", "HowTo", ChrW(&H21BB)
                            'These sheets are always visible, so do nothing
                        Case "CUT", "ASSM", "TEST", "POST-OP", "PACK"
                            'These sheets ought to be visible, so do nothing
                        Case Else
                            mySheet.Visible = xlSheetHidden
                    End Select
                Next
        End Select
    End If

Next
Application.ScreenUpdating = True

Upvotes: 0

Views: 3874

Answers (2)

ib11
ib11

Reputation: 2568

I believe this is the famous case of the need for the very much not recommended DoEvents.

It basically lets Windows process all the messages in queue that are waiting for execution.

It is therefore usually not recommended in bigger applications, but does miracles in simple VBA macros.

Try this:

    Case Else
        mySheet.Visible = xlSheetVisible 
        DoEvents
End Select

Hope it helps.

Upvotes: 0

OldUgly
OldUgly

Reputation: 2119

I also cannot duplicate the error ... but you might try this in the workbook that does generate the error ...

Set oldActive = ActiveSheet
mySheet.Visible = xlSheetVisible
oldActive.Activate

Upvotes: 1

Related Questions