Workbook_BeforeClose Event

[captculver]

I have a project summary workbook that several people will access to update project status. When they try to close the workbook, I would like a message box to come up asking if they have updated the revision date. I have the following code in place, which brings up the message box, but regardless of the response entered the workbook closes. How do I cancel the workbook closure and return to the "Summary" worksheet if the response is "No" to the msgbox question?

Thanks for your help, CC

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim msg As String
Dim myTitle As String
Dim Response As String


msg = "For the projects you updated, did you change the revision date?"
myTitle = "PROJECT UPDATES"
Response = MsgBox(msg, vbExclamation + vbYesNo, myTitle)
If Response = vbNo Then
Sheets("Summary").Activate
Else
Exit Sub
End If

End Sub

 

[VoG]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim msg As String
Dim myTitle As String
Dim Response As String
msg = "For the projects you updated, did you change the revision date?"
myTitle = "PROJECT UPDATES"
Response = MsgBox(msg, vbExclamation + vbYesNo, myTitle)
If Response = vbNo Then
    Cancel = True
    Sheets("Summary").Activate
End If
End Sub

 

[Domski]

Try:

Private Sub Workbook_BeforeClose(Cancel As Boolean)    Dim msg As String
    Dim myTitle As String
    Dim Response As String


    msg = "For the projects you updated, did you change the revision date?"
    myTitle = "PROJECT UPDATES"
    Response = MsgBox(msg, vbExclamation + vbYesNo, myTitle)
    If Response = vbNo Then
        Cancel = True
        Sheets("Summary").Activate
    End If


End Sub

Dom

 

[captculver]

Thanks to both of you; worked perfectly...CC