haddy27
07-31-2008, 07:56 AM
I am attempting to use the Workbook_BeforeClose event, and am struggling to tailor it to my needs.
Basically, if a function isn't performed in the spreadsheet, cell B1 is set to "0".
If B1 is set to 0 when users attempt to close the spreadsheet I want to have a warning to remind them, but also the option to 'cancel' the close and return to the spreadsheet.
The code I have thus far is:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim message As String
If Range("B1") = "0" Then
message = "You have not done xxxxx. Click Cancel to return to spreadsheet or"
message = message & " OK to close the spreadsheet"
answer = MsgBox(prompt:=message, Buttons:=vbOKCancel + vbQuestion)
If answer = vbOK Then ActiveWorkbook.Close
End If
End Sub
So all that happens is the spreadsheet closes regardless of what button is pressed.
I think it is the 'Cancel as Boolean' that is causing me problems but the event won't run without it.
Any ideas??
Thanks
haddy27
Basically, if a function isn't performed in the spreadsheet, cell B1 is set to "0".
If B1 is set to 0 when users attempt to close the spreadsheet I want to have a warning to remind them, but also the option to 'cancel' the close and return to the spreadsheet.
The code I have thus far is:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim message As String
If Range("B1") = "0" Then
message = "You have not done xxxxx. Click Cancel to return to spreadsheet or"
message = message & " OK to close the spreadsheet"
answer = MsgBox(prompt:=message, Buttons:=vbOKCancel + vbQuestion)
If answer = vbOK Then ActiveWorkbook.Close
End If
End Sub
So all that happens is the spreadsheet closes regardless of what button is pressed.
I think it is the 'Cancel as Boolean' that is causing me problems but the event won't run without it.
Any ideas??
Thanks
haddy27