Hide worksheets on close

[bakerman]

How do I hide all worksheets in a workbook when the workbook is being closed so that the next time it is opened only sheet 1 (named TOC ) is visable

 

[Barry Katcher]

Use the Macro Recorder to record a macro that hides all the sheets except TOC. Copy the macro (excluding the first and last lines) to the clipboard.

In the "This sheet" module, paste this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Paste your recorded macro in between the first and last lines.

Save the workbook. When you close the workbook, the macro will hide the desired sheets.

 

[DominicB]

Good afternoon bakerman

Further to Barry's answer use this code between his suggested opening / closing lines :

On Error Resume Next
For Each wksht In ActiveWorkbook.Worksheets
If Not wksht.Name = "TOC" Then Sheets(wksht.Name).Visible = False
Next wksht

To show sheets again make .Visible=False.
For extra security you could use .Visible=xlVeryHidden. This would stop the sheets being listed in the Format > Sheet > Unhide dialog. Just stops users not savvy enough to know better poking their noses into bits they're not supposed to.

HTH

DominicB

 

[bakerman]

Thanks guys, working like a dream