Macro Conditions
Posted by RoB on August 20, 2001 5:32 PM
Ok, question. I have 2 macros in my workbook. One macro automatically closes the workbook after a certain amount of time of inactivity. I also added a macro which gives a message box everytime the workbook is shutdown. I was wondering if there is a way to prevent the message box macro from running if the autoshutdown macro is executed. I'm assuming if there is, it would be in the message box macro code. The codes are kind of weird for the auto shutdown, so if this is not clear enough, please let me know and I'll post the sheet.
Thanks
Posted by Ian on August 21, 2001 12:03 AM
Could you post the 2 codes?
Posted by Robb on August 21, 2001 5:04 AM
Rob
One way is too use the EnableEvents statement (i.e. Application.EnableEvents = False) but it need to be set back to True and, since you are closing the workbook, you cannot do this in Excel97.
The best way would be to rely on the value of a variable that you can set in the "autoshutdown" macro. The massage box code then includes a test for the value and only runs the message box if the value is correct.
It works like this:
You declare a Public variable in the "autoshutdown" macro - I would suggest making it a boolean (true/false). To do this:
- Go to the module in which you have written the "autoshutdown" macro.
-At the very top of the module(before any other code) type
Public MBShow as Boolean
-This declares the variable by the name "MBShow"
-When you have type this, a blue line will appear under it
-Now you need to give "MBShow" a true or false value
-In your "autoshutdown" macro, before the Close statement, type
MBShow = False
-You can now make the MessageBox run/not run according to the value
(I presume you run the MsgBox via the Workbook_BeforeClose event or an Auto_Close macro)
-Amend your messagebox macro to include something like:
If MBShow = True then
MsgBox (whatever you have there)
Else
End If
-Since the "autoshutdown" macro will always set MBShow to False before closing the workbook, your MessageBox will never run if the workbook is closed by that method.
Hope this helps
Regards
