Solved: Workbook_Before Close problem

[haddy27]

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

[Bob Phillips]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim message As String

If Range("B1").Value = 0 Then

message = "You have not done xxxxx. Click Cancel to return to spreadsheet or"
message = message & " OK to close the spreadsheet"
If MsgBox(prompt:=message, Buttons:=vbOKCancel + vbQuestion) = vbCancel Then

Cancel = True
End If
End If

End Sub

[haddy27]

Thanks, thats me sorted now.

haddy 27