Hide Sheets when closing file

  • Thread starter Thread starter Gordon
  • Start date Start date
G

Gordon

Hi...

Is there code that will allow me to hide all sheets except sheet1 when I
close my file. Equally, when opening the file and macros are enabled is there
code that can make all sheets visible but sheet 1 invisible?

Many thanks

Gordon...
 
Hi Gordon,

These should do you; they go in the 'ThisWorkbook' module.

Option Explicit
Private Sub Workbook_Open()
Dim Wks As Object
Application.ScreenUpdating = False
For Each Wks In Sheets
Wks.Visible = True
If Wks.Name = "Sheet1" Then Wks.Visible = xlSheetHidden
Next Wks
Application.ScreenUpdating = True
End Sub
----------------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Wks As Object
For Each Wks In Sheets
If Not Wks.Name = "Sheet1" Then Wks.Visible = xlSheetHidden
Next Wks
ActiveWorkbook.Save
End Sub

Cheers,
JF.
 
Stunning...thanks. This is about the 10th post on this and I was tearing my
hair out.

Thanks again

G
 
Back
Top