Reminder Messages before Workbook Close

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

Try something like this:

Code:

---

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

---

Hi Andrew,

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

Code:

---

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.

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

Quote:

---

Originally Posted by Andrew

How do I call my macro-deleting macro from my Personal Workbook?

---

Try this:

Code:

---

Application.Run "PERSONAL.XLS!MacroName"

---

Thanks DRJ,

Unfortunately I still had some trouble.

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

Code:

---

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

The DeleteAllMacros sub is in ThisWorkbook of Personal.xls right?

Try this then:

Code:

---

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

---

Bingo!

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

Consider this one SOLVED!

Andrew

Glad to help :)

Take Care