Message Box When Closing Excel

[sn281]

Hi all,

Thank you in advance for your help.

I'm okay with VBA but I always get a bit confused with these message boxes when they link.

So what I want to do is to have a message box that pops up when this Excel file is attempted to close that says "Have you uploaded the data?" "Yes" or "No"

If they click "Yes" then Excel closes, if they click "No", it then leads onto the next message box that then says "Would you like to now?".
If they click "Yes" it runs the macro called "gprc", otherwise if they click "No" it then closes Excel.


I think to do this Workbook_BeforeClose will be used, but I just get very confused with the 'if' and 'when' and stuff like that with the dependent message boxes. If someone could help me out or try to explain how to do it I'd really appreciate it, and hopefully I'll learn once and for all from it!

Thank you so much for your help in advance!
sn281

 

[jaybee3]

Hey sn281,

I think this will do the trick:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If (MsgBox("Have you uploaded the data?", vbYesNo) = vbNo) Then
        If (MsgBox("Would you like to now?", vbYesNo) = vbYes) Then
            gprc
        End If
    End If
    
End Sub

 

[VoG]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Have you uploaded the data?", vbYesNo) = vbYes Then Exit Sub
If MsgBox("Would you like to now?", vbYesNo) = vbYes Then Call gprc
End Sub

 

[sn281]

Hi jaybee3,

Thanks for the quick response. I've put this code into a new module, but when I close the workbook it doesn't actually pop up. It just closes.
I'm on Windows 7, Excel 2010.

Do you know why this is happening? Have I done something stupid? I copied the cose into a new module....

Thanks again,
sn281

 

[VoG]

Press ALT + F11, in the Project window double click ThisWorkbook then paste in the code.

 

[sn281]

That works perfectly. Thank you so much. What exactly is the difference between the module, 'ThisWorkbook' and the different sheets in VBA?
Thanks again,
sn281

 

[VoG]

Take a look Events In Excel VBA

 

[sn281]

Thanks a lot VoG. Have a great day!

 

[sandwhich]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Have you uploaded the data?", vbYesNo) = vbYes Then Exit Sub
If MsgBox("Would you like to now?", vbYesNo) = vbYes Then Call gprc
End Sub

Would it be possible to have it move thru multiple questions? Like:
Did you save Item 1?
Did you save Item 2?
Did you save Item 3?
If yes to question 3 then excel would close?

Thanks in advance!