Hello. To ensure users have macros enabled, I have saved my file with all
sheets except a Warning sheet to xlveryhidden, with an Open Workbook event
that unhides all sheets.

I also wanted to veryhide all sheets again before save so the user can't
save the file with the sheets unhidden, and therefore allowing them to get
into the file next time with macros disabled.

This works great, except after the Before Save event fires, the Warning
sheet is displayed. So I added a button to that sheet that the user clicks
to unhide all sheets again.

Can that button be automatically pressed after the Save event takes place?
Sort of an After Save event that unhides all sheets again? I have read a
little about App.EnbleEvents, but don't know the proper syntax or use.
Thanks! My code is below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim ws As Worksheet
Warning.Visible = True
For Each ws In Worksheets
If Not ws.Name = Warning.Name Then ws.Visible = xlVeryHidden
Next ws
End Sub

Sub Unhide_Sheets() 'Button to press after save
Dim ws As Worksheet
For Each ws In Worksheets
ws.Visible = True
Next ws
Warning.Visible = xlVeryHidden
End Sub

In the BeforeSave event, hide all the sheets.

Application.EnableEvents = False
Thisworkbook.Save
Application.EnableEvents = True
Cancel = True
' now unhide the sheets

So anytime the workbook is saved, it is saved with sheets hidden.

You might have to check the SaveAsUI variable to see if the user is doing a
SaveAs. If so, if this is allowed, you would need to use GetSaveAsfilename
to get the name for the file, then save it yourself.

--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Hello. To ensure users have macros enabled, I have saved my file with all
sheets except a Warning sheet to xlveryhidden, with an Open Workbook event
that unhides all sheets.

I also wanted to veryhide all sheets again before save so the user can't
save the file with the sheets unhidden, and therefore allowing them to get
into the file next time with macros disabled.

This works great, except after the Before Save event fires, the Warning
sheet is displayed. So I added a button to that sheet that the user
clicks
to unhide all sheets again.

Can that button be automatically pressed after the Save event takes place?
Sort of an After Save event that unhides all sheets again? I have read a
little about App.EnbleEvents, but don't know the proper syntax or use.
Thanks! My code is below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim ws As Worksheet
Warning.Visible = True
For Each ws In Worksheets
If Not ws.Name = Warning.Name Then ws.Visible = xlVeryHidden
Next ws
End Sub

Sub Unhide_Sheets() 'Button to press after save
Dim ws As Worksheet
For Each ws In Worksheets
ws.Visible = True
Next ws
Warning.Visible = xlVeryHidden
End Sub

Thanks Tom. SaveAs is definitely a possibility (more like a probability).
And I would want the code to apply to the SaveAs'd file as well. So maybe
something like this to capture SaveAsUI:

Application.EnableEvents = False
If SaveAsUI Then
MySaveAs = Application.ActiveWorkbook.Ful*lName
MySaveAs = Left(MySaveAs, Len(MySaveAs) - 4)
MySaveAs = Application.GetSaveAsFilename
(MySaveAs, "Excel Files (*.xls), *.xls")
Application.ActiveWorkbook.Sav*eAs MySaveAs
Else
Application.ActiveWorkbook.Sav*e
End If
Application.EnableEvents = True

"Tom Ogilvy" wrote in message
...
In the BeforeSave event, hide all the sheets.

Application.EnableEvents = False
Thisworkbook.Save
Application.EnableEvents = True
Cancel = True
' now unhide the sheets

So anytime the workbook is saved, it is saved with sheets hidden.

You might have to check the SaveAsUI variable to see if the user is doing
a
SaveAs. If so, if this is allowed, you would need to use
GetSaveAsfilename
to get the name for the file, then save it yourself.

--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Hello. To ensure users have macros enabled, I have saved my file with
all
sheets except a Warning sheet to xlveryhidden, with an Open Workbook
event
that unhides all sheets.

I also wanted to veryhide all sheets again before save so the user can't
save the file with the sheets unhidden, and therefore allowing them to
get
into the file next time with macros disabled.

This works great, except after the Before Save event fires, the Warning
sheet is displayed. So I added a button to that sheet that the user
clicks
to unhide all sheets again.

Can that button be automatically pressed after the Save event takes
place?
Sort of an After Save event that unhides all sheets again? I have read
a
little about App.EnbleEvents, but don't know the proper syntax or use.
Thanks! My code is below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim ws As Worksheet
Warning.Visible = True
For Each ws In Worksheets
If Not ws.Name = Warning.Name Then ws.Visible = xlVeryHidden
Next ws
End Sub

Sub Unhide_Sheets() 'Button to press after save
Dim ws As Worksheet
For Each ws In Worksheets
ws.Visible = True
Next ws
Warning.Visible = xlVeryHidden
End Sub