Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
close workbook without closing excel and stop recursive function | Excel Discussion (Misc queries) | |||
STOP WORKBOOK CLOSING | Excel Discussion (Misc queries) | |||
Validation to stop workbook closing | Excel Discussion (Misc queries) | |||
Stop Workbook from closing. | Excel Programming | |||
closing excel after closing a workbook | Excel Programming |