Stop the workbook from closing in Auto_Close

[gp133]

I need the the auto_close procedure to check stuff before the workbook closes
then give the user the option to not close based on the results. I'm sure
it'll be something simple but I just don't know what it is. The code so far
is as follows...

Sub auto_close()

If Range("H4").Value <> "ALL JOBS COMPLETE" Then
msg1 = MsgBox("One or more jobs are still outstanding." & Chr(10) & _
"Are you sure you want to exit?", vbYesNo, "Jobs Outstanding")
If msg1 = vbNo Then
[insert "no close" code here]
Else
GoTo 10
End If
End If

10 End Sub

 

[NickHK]

I find it easier to use the _BeforeClose event of ThisWorkbook. e.g.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Worksheets("WhichSheet").Range("H4").Value <> "ALL JOBS COMPLETE" Then
msg1 = MsgBox("One or more jobs are still outstanding." & vbNewLine & _
"Are you sure you want to exit?", vbYesNo, "Jobs Outstanding")
If msg1 = vbNo Then
Cancel = True
End If
End If
End Sub

You should specify which sheet the range "H4" refers to, otherwise it is the
active sheet, which may or may not correct.

NickHK

 

[gp133]

Thanks a lot for your help. I knew it would be simple!

I find it easier to use the _BeforeClose event of ThisWorkbook. e.g.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Worksheets("WhichSheet").Range("H4").Value <> "ALL JOBS COMPLETE" Then
msg1 = MsgBox("One or more jobs are still outstanding." & vbNewLine & _
"Are you sure you want to exit?", vbYesNo, "Jobs Outstanding")
If msg1 = vbNo Then
Cancel = True
End If
End If
End Sub

You should specify which sheet the range "H4" refers to, otherwise it is the
active sheet, which may or may not correct.

NickHK

I need the the auto_close procedure to check stuff before the workbook closes
then give the user the option to not close based on the results. I'm sure
it'll be something simple but I just don't know what it is. The code so far
is as follows...

Sub auto_close()

If Range("H4").Value <> "ALL JOBS COMPLETE" Then
msg1 = MsgBox("One or more jobs are still outstanding." & Chr(10) & _
"Are you sure you want to exit?", vbYesNo, "Jobs Outstanding")
If msg1 = vbNo Then
[insert "no close" code here]
Else
GoTo 10
End If
End If

10 End Sub