Thread: Creating Message Box after Saving

If I understand correctly, put this in the ThisWorkbook module (see VBE Project window):

Private Sub Workbook_BeforeClose(Cancel As Boolean)

x

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

x

End Sub

and this in a normal module

Sub x()

MsgBox "Text here", vbOKOnly

End Sub

But what is the point of this?

Originally Posted by smh242

I just need a message box to appear after someone saves the Excel document. The message box will only have text and probably an "OK" button for the user to press. The box itself is completely independent from the "save", meaning that once the user pressed "save", the document is saved regardless if the user presses "OK" in the message box.

So it's a pretty simple message box but when I looked on forums, the message boxes introduced were a little too complex for my simple request.

Another item, can you inform me what I need to change to make the message box appear if someone closes the Excel spreadsheet? So rather than pressing Save, what if they close the spreadsheet. So please help with these two requests.

Thanks so much.

Hello

I'll assume you've never used VBA in your life. Open up VBA (press ALT+F11 from Excel). In the Project Explorer double click "ThisWorkbook". If you can't see this make sure the Project Explorer is visible by going to View > Project Explorer. Then maximize the folder "Microsoft Excel Objects". "ThisWorkbook" should be there.

A blank page will open. Near the top there should be two drop down boxes. The first should say "(General)". Change this to "Workbook" then select "BeforeSave". This event is fired when the user saves the document.

In the sub that appears add your message box. E.g.:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "Your message.", vbOKOnly, "Your title"
End Sub

For when the user closes the workbook. Do the same but this time choose "BeforeClose" and stick your code in that new sub. E.g.:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    MsgBox "Your message.", vbOKOnly, "Your title"
End Sub

EDIT: Sorry, StephenR beat me to it.

You will probably be better off in this situation to use Conditional Formatting and/or Data Validation to ensure correct input. If a user doesn't enable macros then your warning messages won't appear.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save