Smartening up of VBA

[SuperFerret]

Hello,

I have managed to put together code from this site, and added in some extra bits that I needed.

Basically what the macro should be doing:
1) Open the Workbook to sheet MACROS if Macros are not enabled - keeping all other sheets hidden.
2) If Macros enabled, show the Discontinued and ALL LIVE. sheets - not MACROS sheet
3) Before saving/closing - unfilter all columns on Discontinued sheet
4) Reset to MACROS sheet

This works, but I'm not anywhere near a genius on VBA yet so I don't know if this can be neatened/cleaned up any, and if so how?

Can anyone offer me advice?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False
        Call HideSheets
        .ScreenUpdating = True
        .EnableCancelKey = xlInterrupt
    End With
    ThisWorkbook.Save
    ThisWorkbook.Saved = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim WS As Worksheet
    For Each WS In Worksheets
         WS.AutoFilterMode = False
    Next WS
    With Application
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False
        Call HideSheets
        .ScreenUpdating = True
        .EnableCancelKey = xlInterrupt
    End With
 
    ThisWorkbook.Save
    ThisWorkbook.Saved = True
End Sub
Private Sub Workbook_Open()
 
    'Unhide all worksheets
 
            With Application
                .EnableCancelKey = xlDisabled
                .ScreenUpdating = False
 
                Call UnhideSheets
 
                .ScreenUpdating = True
                .EnableCancelKey = xlInterrupt
            End With
 
End Sub
Private Sub HideSheets()
Dim Sheet As Object
    With Sheets("MACROS")
        .Visible = xlSheetVisible
            For Each Sheet In Sheets
                If Not Sheet.Name = "MACROS" Then
                    Sheet.Visible = xlSheetVeryHidden
                End If
            Next
        Set Sheet = Nothing
    End With
End Sub
Private Sub UnhideSheets()
    Sheets("Discontinued").Visible = xlSheetVisible
    Sheets("MACROS").Visible = xlSheetVeryHidden
    Sheets("ALL LIVE.").Visible = xlSheetVisible
 
End Sub

 

[GTO]

Presuming the other visible sheet does not need any filtering left on, have you tried just tacking it in before making the sheets hidden?

For Each wksWorksheet In ThisWorkbook.Worksheets
    If Not wksWorksheet.CodeName = "shtForceEnable" Then
        wksWorksheet.AutoFilterMode = False
        wksWorksheet.Visible = xlSheetVeryHidden
    End If
Next

 

[SuperFerret]

Doh! Only place I hadn't tried!

Thanks again GTO, you are an absolute star!