Consulting

Results 1 to 9 of 9

Thread: Reminder Messages before Workbook Close

  1. #1
    VBAX Regular Andrew's Avatar
    Joined
    Jun 2004
    Location
    Nagoya, Japan
    Posts
    13

    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

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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

  3. #3
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    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.

  4. #4
    VBAX Regular Andrew's Avatar
    Joined
    Jun 2004
    Location
    Nagoya, Japan
    Posts
    13

    Thanks

    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

  5. #5
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Quote Originally Posted by Andrew

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

    Application.Run "PERSONAL.XLS!MacroName"

  6. #6
    VBAX Regular Andrew's Avatar
    Joined
    Jun 2004
    Location
    Nagoya, Japan
    Posts
    13
    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

  7. #7
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    The DeleteAllMacros sub is in ThisWorkbook of Personal.xls right?

    Try this then:

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

  8. #8
    VBAX Regular Andrew's Avatar
    Joined
    Jun 2004
    Location
    Nagoya, Japan
    Posts
    13
    Bingo!

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

    Consider this one SOLVED!

    Andrew

  9. #9
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Glad to help

    Take Care

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •