How to popup a message when excel closes

[Alice21]

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!

[tigeravatar]

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

[Alice21]

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??

[Dave H9]

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.

[Alice21]

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).

[Dave H9]

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.

[Alice21]

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

[tigeravatar]

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

[Alice21]

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

[tigeravatar]

Alice21,

You would replace that basic msgbox with the buttons msgbox:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar