PDA

View Full Version : Solved: Msgbox to open when excel closes.



bacon
09-17-2004, 01:06 AM
Hello,

I am after a small bit of VBA to help me with a problem. I would like a message box to appear on a spreadsheet when it closes that says "Have you updated the Spreadsheet" then a Yes No option. If the user clicks Yes it will fire a Macro. If they click no the spreadsheet will close as normal.

is this simple to do?

Many thanks

Bacon

Jacob Hilderbrand
09-17-2004, 01:54 AM
Place this code in the ThisWorkbook Code Window:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Prompt As String
Dim Title As String
Dim MyResponse As VbMsgBoxResult
Prompt = "Have you updated the Spreadsheet"
Title = "Insert Title Here"
MyResponse = MsgBox(Prompt, vbYesNo + vbQuestion, Title)
If MyResponse = vbYes Then
Call Macro1
End If

End Sub


Change Macro1 to the name of the macro you want to run if the user presses yes.

bacon
09-17-2004, 03:19 AM
works a treat

Many thanks

Jacob Hilderbrand
09-17-2004, 05:07 AM
You're Welcome

Take Care

xneurosis
01-24-2013, 03:05 AM
Thanks!