PDA

View Full Version : Solved: Reminder Messages before Workbook Close



Andrew
09-21-2004, 06:30 PM
Hello everyone,

I would very much like a series of reminder messages to make sure I checked everything before I close a Workbook.

The first one should be "Did you remember to change the Header Date?"

If yes, then "Did you remember to remove excess sheets?", else go back to the Workbook.

Finally, I have a macro in my Personal Workbook that deletes all macros in the Active Workbook. Is it possible to use this macro to remove all macros including the above one also before the Workbook is closed? (I think the above macro would be a BeforeClose macro, which would need to call my "DeleteAllMacros" macro?)

If it is not possible, another message box that says "Well, don't forget to close me too!", and when I push "Okay", it returns to the Workbook so I can delete it manually.

I will also need an escape option for each mesage box in case I want to close the Workbook without deleting anything. Perhaps an extra button that says "Escape?", which if pushed asks "Are your sure?", if yes, close the Workbook, else go back to the start. (It does not matter if I change the Header Date, but I would prefer not to remove any sheets if I choose to "escape")

Hopefully I am not completely mad in addition to being absent-minded. If anyone can help or has some non-violent suggestions (joke), I would truly appreciate it. Thanks. :)

Andrew

Jacob Hilderbrand
09-21-2004, 07:14 PM
Try something like this:


Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Proceed As VbMsgBoxResult
Proceed = MsgBox("Did you remember to change the Header Date?", vbQuestion + vbYesNo)
If Proceed = vbNo Then
Cancel = True
Exit Sub
End If
Proceed = MsgBox("Did you remember to remove excess sheets?", vbQuestion + vbYesNo)
If Proceed = vbNo Then
Cancel = True
Exit Sub
End If

Application.Run 'Your Delete Code Macro

End Sub

Zack Barresse
09-21-2004, 07:31 PM
Hi Andrew,

Take a look at this, which goes into your ThisWorkbook module ...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Did you put your shoe's on today?", vbYesNo) = vbYes Then
If MsgBox("Did you put your pants on?", vbYesNo) = vbYes Then
If MsgBox("Did you put your shirt on?", vbYesNo) = vbYes Then
If MsgBox("And your cap?", vbYesNo) = vbYes Then GoTo closeMe
End If
End If
End If
Cancel = True
Exit Sub
closeMe:
Call deleteAllMacros
Application.EnableEvents = False
ThisWorkbook.Close True
End Sub

Private Sub Workbook_Open()
Application.EnableEvents = True
End Sub

As for your 'Escape' button, I'd highly suggest that you add it to the beginning of your macro to delete all macros. One large caveat about this, the EnableEvents = False is there so the close macro will not fire twice (which it will if you take it out). Hence the addition of the turning it back on in the open macro. Problem?: If you use other workbooks in the meantime (and using Xl XP or up) your EnableEvents will remain false until you set it back. See, no more macros will fire after the ThisWorkbook.Close True, because we just closed the book, and there's nothing to run that is open!

Anyway, HTH.

Andrew
09-21-2004, 09:17 PM
Hi guys and thanks,

(Zack, how did you know about my pants?)

How do I call my macro-deleting macro from my Personal Workbook? Or should I copy the macro-deleting macro (bit of a mouthful) and put it somewhere in the Active Workbook?

Sorry if I am totally clueless, maybe I should think of political career instead? :)

Andrew

Jacob Hilderbrand
09-21-2004, 09:47 PM
How do I call my macro-deleting macro from my Personal Workbook?
Try this:


Application.Run "PERSONAL.XLS!MacroName"

Andrew
09-21-2004, 10:55 PM
Thanks DRJ,

Unfortunately I still had some trouble.

Here's the name of the macro I want to use.

Sub DeleteAllMacros()

So I should just write Application.Run "PERSONAL.XLS!DeleteAllMacros" right?

(I tried this with Zack's code too)

The code is placed in ThisWorkbook. Hope this makes sense, thanks again. :)

Andrew

Jacob Hilderbrand
09-21-2004, 10:59 PM
The DeleteAllMacros sub is in ThisWorkbook of Personal.xls right?

Try this then:


Application.Run "PERSONAL.XLS!ThisWorkbook.DeleteAllMacros"

Andrew
09-22-2004, 12:17 AM
Bingo!

DRJ and Zack, thanks so much for your help, you guys are great.

Consider this one SOLVED!

Andrew

Jacob Hilderbrand
09-22-2004, 01:03 AM
Glad to help :)

Take Care