CODEWITHSUNDEEP
excelvba
Tim
Tim

Reputation: 111

Hiding WorkSheets Programmatically in Excel

I am trying to hide all of the sheets in a Workbook when I close Excel. The code I am using hides all of the sheets until it gets to the last one. Then it gives me a Run-time error '1004' Method'Visible'of'Object'_Worksheet failed. I thought I may have had to many sheets so I deleted most of them and nothing changes except I had fewer sheets. This is the code I am trying to use. I placed it in "ThisWorkbook".

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet


For Each sh In Sheets
    If StrComp(sh.Name, C_INTRO_SHEETNAME, vbTextCompare) = 0 Then
        sh.Visible = xlSheetVisible
    Else
        sh.Visible = xlSheetVeryHidden
    End If
Next sh

ActiveWorkbook.Sheets("Welcome").Visible = True
SaveState

End Sub

I am using ActiveWorkbook.Sheets("Welcome").Visible = True to unhide the welcome sheet that explains how to enable macros then I save it so when it opens I only have the welcome sheet visible. When it opens I use this code to unhide the sheets and hide the welcome sheet.

Private Sub Workbook_Open()
Dim sh As Worksheet

For Each sh In Sheets
    sh.Visible = True
Next sh

ActiveWorkbook.Sheets("Welcome").Visible = xlSheetVeryHidden
Worksheets("Service").Protect UserInterfaceOnly:=True
Worksheets("Service").Activate

End Sub

Hopefully this will happen after they enable macros, otherwise there is no need to unhide the other sheets.

Upvotes: 0

Views: 3751

Answers (1)

Tim
Tim

Reputation: 111

Thanks for the help rdhs. If you post your answer I will accept it. Here are my final code changes.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet

Application.ScreenUpdating = False
'' If it is the Welcome sheet then make it visible, else hide it.
For Each sh In Sheets
    If StrComp(sh.Name, INTRO_SHEETNAME, vbTextCompare) = 0 Then
            sh.Visible = xlSheetVisible
    Else
        sh.Visible = xlSheetVeryHidden
    End If
Next sh
'' Call subroutine to save changes.
SaveState

End Sub

Private Sub Workbook_Open()
Dim sh As Worksheet

Application.ScreenUpdating = False
'' Make all the sheets visible.
For Each sh In Sheets
    sh.Visible = True
Next sh
'' Hide the welcome sheet.
ActiveWorkbook.Sheets("Welcome").Visible = xlSheetVeryHidden
Worksheets("Service").Protect UserInterfaceOnly:=True
Application.ScreenUpdating = True
Worksheets("Service").Activate

End Sub

Code placed in "ThisWorkbook" to be called upon opening and closing the workbook.

Upvotes: 1

Related Questions