View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Steph[_3_] Steph[_3_] is offline
external usenet poster
 
Posts: 312
Default After Save Event help

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