Is there a generic VB code that will hide all spreadsheets in a workbook but
one. I use this method to prevent users from using the file after disabling
macros. If they Disable Macros, all they see is a spreadsheet with a message
saying 'to use this file they have to Enable Macros'. When Macros are enable
and the file is closed/saved, all sheets, except the one with the message,
are made invisible and the workbook protected (locked), and when the file is
opened with Macros Enabled all sheets except the one with message are made
visible and the workbook is unlocked (not protected).

The problem is that a user can add a new spreadsheet to the workbook and the
code I use will not make the new spreadsheet invisible on closing. The code
I use is:

sheets("Sheet Name").visible=false

Is there a way to make all sheets invisible or a toggle command to reverse
the existing visible property for all sheets in a workbook? Or perhaps,
there is a better approach. Any assistance will be appreciated.

Try something like

Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
If WS.Name < "SheetNotToHide" Then
WS.Visible = xlSheetVeryHidden ' xlSheetVisible to make
visible
End If
Next WS


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"JR_06062005" wrote in
message
...
Is there a generic VB code that will hide all spreadsheets in a
workbook but
one. I use this method to prevent users from using the file
after disabling
macros. If they Disable Macros, all they see is a spreadsheet
with a message
saying 'to use this file they have to Enable Macros'. When
Macros are enable
and the file is closed/saved, all sheets, except the one with
the message,
are made invisible and the workbook protected (locked), and
when the file is
opened with Macros Enabled all sheets except the one with
message are made
visible and the workbook is unlocked (not protected).

The problem is that a user can add a new spreadsheet to the
workbook and the
code I use will not make the new spreadsheet invisible on
closing. The code
I use is:

sheets("Sheet Name").visible=false

Is there a way to make all sheets invisible or a toggle command
to reverse
the existing visible property for all sheets in a workbook? Or
perhaps,
there is a better approach. Any assistance will be
appreciated.

Sheets("Enabler Sheet").Visible = xlSheetVisible
For Each sh In Activeworkbook.Sheets
If sh.Name < "Enabler Sheet" Then
sh.visible = xlSheetVeryHidden
End If
Next For

Just do it in reverse on startup.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"JR_06062005" wrote in message
...
Is there a generic VB code that will hide all spreadsheets in a workbook
but
one. I use this method to prevent users from using the file after
disabling
macros. If they Disable Macros, all they see is a spreadsheet with a
message
saying 'to use this file they have to Enable Macros'. When Macros are
enable
and the file is closed/saved, all sheets, except the one with the message,
are made invisible and the workbook protected (locked), and when the file
is
opened with Macros Enabled all sheets except the one with message are made
visible and the workbook is unlocked (not protected).

The problem is that a user can add a new spreadsheet to the workbook and
the
code I use will not make the new spreadsheet invisible on closing. The
code
I use is:

sheets("Sheet Name").visible=false

Is there a way to make all sheets invisible or a toggle command to reverse
the existing visible property for all sheets in a workbook? Or perhaps,
there is a better approach. Any assistance will be appreciated.