G
Guest
I need to force the user to enable macros.
I have a before save procedure in my workbook which hides all the sheets
except for one with a message telling the user to enable macros, then saves
before unhiding the sheets again.
When I click the save button, the procedure is triggered, and the workbook
is saved with sheets hidden, but if I call the procedure from the beforeclose
event procedure, and step through, all the code appears to run correctly, and
in the right order, but has no effect. ie the sheets are not hidden and the
workbook is not saved.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Sheets("Macros Disabled").Visible = True
For Each ws In ThisWorkbook.Sheets
If ws.Name <> "Macros Disabled" Then ws.Visible = False
Next
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
For Each ws In ThisWorkbook.Sheets
ws.Visible = True
Next
Sheets("Macros Disabled").Visible = False
Sheets("Calendar").Visible = False
End Sub
I have a before save procedure in my workbook which hides all the sheets
except for one with a message telling the user to enable macros, then saves
before unhiding the sheets again.
When I click the save button, the procedure is triggered, and the workbook
is saved with sheets hidden, but if I call the procedure from the beforeclose
event procedure, and step through, all the code appears to run correctly, and
in the right order, but has no effect. ie the sheets are not hidden and the
workbook is not saved.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Sheets("Macros Disabled").Visible = True
For Each ws In ThisWorkbook.Sheets
If ws.Name <> "Macros Disabled" Then ws.Visible = False
Next
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
For Each ws In ThisWorkbook.Sheets
ws.Visible = True
Next
Sheets("Macros Disabled").Visible = False
Sheets("Calendar").Visible = False
End Sub