Hiding worksheets when opening excel using VBA code

[ProudHook]

Hi

I have built a workbook with VBA code that when you open it, it only shows the first worksheet (there are 4 worksheets in total in the workbook). The remaining 3 worksheets are not visible until you click a command button.

When you click the command button on the first worksheet, the remaining worksheets become unhidden as expected.

Once the remaining worksheets are unhidden, I make some changes to the worksheets and then as I exit the workbook I save changes.

However I find that when I reopen the workbook all of the hidden sheets are visible straight away - this happens inconsistently when opening the workbook (i.e. sometimes they are visible, sometimes they aren't). I find that the probability of the worksheets being visible upon opening increases if I exit the workbook by clicking the X at the top right corner of Excel and press in the pop up window.

Does anyone know why this would be occurring? It is critical that the sheet are not visible until the command button is accepted.

My code is as follows:

Workbook:

Private Sub Workbook_Open()
Sheets("Summary").Visible = False
Sheets("Annual").Visible = False
Sheets("Monthly").Visible = False
End Sub

Command Button 1

Private Sub CommandButton1_Click()
ThisWorkbook.Worksheets("Summary").Visible = xlSheetVisible
ThisWorkbook.Worksheets("Annual").Visible = xlSheetVisible
ThisWorkbook.Worksheets("Monthly").Visible = xlSheetVisible
End Sub

Command Button 2

Private Sub CommandButton2_Click()
ActiveWorkbook.Close True
Application.Quit
End Sub

Thanks

Doug

 

[dmt32]

Hi
Before you save & close the workbook you need to re-hide the sheets

try following updates to your codes


Thisworkbook Module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 ShowSheets (xlSheetVeryHidden)
End Sub


Private Sub Workbook_Open()
 ShowSheets (xlSheetVisible)
End Sub

Sheets Code Page

Private Sub CommandButton1_Click()
    ShowSheets (xlSheetVisible)
End Sub


Private Sub CommandButton2_Click()
    ActiveWorkbook.Close True
    Application.Quit
End Sub

Standard Module

Sub ShowSheets(ByVal VisibleState As XlSheetVisibility)
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets(Array("Summary", "Annual", "Monthly"))
        sh.Visible = VisibleState
    Next sh
End Sub

Dave

 

[ProudHook]

Dave

Thanks very much, I was able to get it to work.