Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
close workbook without saving or promt to save | Excel Discussion (Misc queries) | |||
Option in Excel to save a close a workbook inactive for 5 minutes | Excel Discussion (Misc queries) | |||
Excel 2002 Automate shared workbook at close to save changes to new document | Excel Programming | |||
Close a workbook without saving macros | Excel Programming | |||
Close Workbook without displaying message to Save | Excel Programming |