Macros on workbook close and save
 

I wish to force the users to use a macro and so I have adopted the suggestion
I found here for using a message sheet warning the user to activate the
macro, and a startup macro that hides the warning message and unhides all the
other sheets.

In order to give the user the option of leaving without saving, I have tried
to install a macro that restores shows the warning sheet hides the others (as
I want it to be saved for the next open) and returns the user to where they
were in the workbook.
(this I found in this community - thanks).

I use a BeforeSave that does the hiding and unhiding, and then an Activate
in the warning worksheet, which calls a sub that returns the user to where
they were.

My problem is that if the user tries to close the workbook and opts to save
their changes, then the warning sheet appears, the workbook saves, and
closes, But then the activate / deactivate macros warning appears, and upon
clicking activate, the workbook re-opens ready for use.

Sorry, not that easy to explain.
Has anyone any ideas how I can get around this?

Adrian

Macros on workbook close and save
 

Without the code, it is difficult to be sure, but I think I see what you
mean.

I would change things to this logical order in Beforeesave event

- disable events - Application.EnableEvents = False
- hide and unhide as required (no event will fire)
- run the macro to return the user directly (not from activate event)
- ask if they want to save, if not set Cancel to True
- enable events

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ade P" wrote in message
...
I wish to force the users to use a macro and so I have adopted the
suggestion
I found here for using a message sheet warning the user to activate the
macro, and a startup macro that hides the warning message and unhides all
the
other sheets.

In order to give the user the option of leaving without saving, I have
tried
to install a macro that restores shows the warning sheet hides the others
(as
I want it to be saved for the next open) and returns the user to where
they
were in the workbook.
(this I found in this community - thanks).

I use a BeforeSave that does the hiding and unhiding, and then an Activate
in the warning worksheet, which calls a sub that returns the user to where
they were.

My problem is that if the user tries to close the workbook and opts to
save
their changes, then the warning sheet appears, the workbook saves, and
closes, But then the activate / deactivate macros warning appears, and
upon
clicking activate, the workbook re-opens ready for use.

Sorry, not that easy to explain.
Has anyone any ideas how I can get around this?

Adrian