+ Reply to Thread
Results 1 to 10 of 10

How to popup a message when excel closes

  1. #1
    Forum Contributor Alice21's Avatar
    Join Date
    04-22-2010
    Location
    Wales, United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    173

    How to popup a message when excel closes

    Hi

    I'm a complete novice when it comes to VBA codes but I am trying to get a message to pop up upon closing of an excel workbook.

    Can anyone help me with the code please?

    Sorry for sounding dull!

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    re: How to popup a message when excel closes

    Alice21,

    You would need to add code to the Workbook_BeforeClose event. I've attached an Excel file containing instructions for where to put the following code:

    Please Login or Register  to view this content.


    Hope that helps,
    ~tigeravatar
    Attached Files Attached Files

  3. #3
    Forum Contributor Alice21's Avatar
    Join Date
    04-22-2010
    Location
    Wales, United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    173

    Re: How to popup a message when excel closes

    Thanks for that, it works well.

    Although my message is asking colleagues to ensure they have completed certain columns and at the moment the only button they can then press is 'OK', then the workbook closes. However if they have forgot to enter the data they would then have to re-open the workbook. Is there a way to change the buttons to say 'Change figures' and 'Continue to close'....or something alon these lines??

  4. #4
    Forum Contributor Dave H9's Avatar
    Join Date
    03-10-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    418

    Re: How to popup a message when excel closes

    Hi Alice,

    Try the attached. The code checks cell N1 if true (ie not all cells populated) it will do nothing, if false (ie all relevant cellspopulated) it will save and close the workbook.
    Attached Files Attached Files

  5. #5
    Forum Contributor Alice21's Avatar
    Join Date
    04-22-2010
    Location
    Wales, United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    173

    Re: How to popup a message when excel closes

    Hi

    Thanks but that will not work for my workbook. For example:

    I have one column (B) which contains the number of beds that should be on a hospital ward. I have another column (C) that should contain the number of beds closed on a ward, if any.

    I want my message to prompt the user on closing to check column B for accuracy and to complete column C if neccessary. I have a VBA code which works fine for this but the only option the message box gives is 'OK'. I would like to have two options in the message box, one to say Continue (which will then close the workbook) and one to say Amend figures (which will not close the work book).

  6. #6
    Forum Contributor Dave H9's Avatar
    Join Date
    03-10-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    418

    Re: How to popup a message when excel closes

    Can you post a censored worksheet. It will be easier to help with the data and layout you are using.

    In the sheet I posted if you change the 'B's to 'C's in the formula in cell N1 the prompt message will appear if no data is entered in the relevant cells in column C

    Please Login or Register  to view this content.
    Add asmany cells as you need checking.

  7. #7
    Forum Contributor Alice21's Avatar
    Join Date
    04-22-2010
    Location
    Wales, United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    173

    Re: How to popup a message when excel closes

    I just need a PROMPT message, like i currently have but i just want to have two option buttons, not just 'OK'.

    It doesn't have to be linked to data contained in cells.

    Friday 15.04.11.xls

    Thanks

  8. #8
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: How to popup a message when excel closes

    Alice21,

    To create a popup message box that has two buttons, you can use the following:

    Please Login or Register  to view this content.


    Hope that helps,
    ~tigeravatar

  9. #9
    Forum Contributor Alice21's Avatar
    Join Date
    04-22-2010
    Location
    Wales, United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    173

    Re: How to popup a message when excel closes

    Thanks. How do I put that code onto this code?

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    MsgBox "This workbook is closing."

    End Sub


    When I've tried it causes an error

  10. #10
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: How to popup a message when excel closes

    Alice21,

    You would replace that basic msgbox with the buttons msgbox:

    Please Login or Register  to view this content.


    Hope that helps,
    ~tigeravatar

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1