Stop the workbook from closing in Auto_Close
 

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

Stop the workbook from closing in Auto_Close
 

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" wrote in message
...
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

Stop the workbook from closing in Auto_Close
 

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

"NickHK" wrote:

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" wrote in message
...
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