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

Hi
you may post your code

--
Regards
Frank Kabel
Frankfurt, Germany

"Rob" schrieb im Newsbeitrag
...
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 think I did Frank. The only other bit is the bit calling it from the
workbook close event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Save
End Sub

I have another problem with this though; do you know a good way to stop
excel asking me if i want to save changes? I don't want to disable events,
cause that could cause problems later.


"Frank Kabel" wrote:

Hi
you may post your code

--
Regards
Frank Kabel
Frankfurt, Germany

"Rob" schrieb im Newsbeitrag
...
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