Stop the workbook from closing in Auto_Close

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Thanks a lot for your help. I knew it would be simple!

NickHK said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top