Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing Form and prevention
Here is the code I have:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.EnableEvents = False MsgBox "You have clicked the X in the upper right corner!" MsgBox "This will close the Excel Program, and all subsequent workbooks opened within." MsgBox "If this was an accident, or you do not want to close all the workbooks then click on the CANCEL button." MsgBox "Otherwise click the OK button, and everything will be closed.", vbOKCancel If vbOK = 1 Then Exit Sub If vbOK = 0 Then Application.Quit End If End Sub This partially does what I am wanting it to do. The purpose of this is if one of the users has multiple workbooks open, and accidentally hits one of the x's in the corner that it gives them a message of what they did. Then Depending whether they hit cancel or ok decides the outcome. If they didn't mean to hit the x, and want to keep the form open then they hit the cancel button. If they do want to close all of it down then they hit ok. The ok part works as intended obviously. The problem is if they hit the cancel button they get the save dialog box. If they hit yes they save it. If they hit no even though they don't want to close the application it does anyway. With cancel all remains open. Is there a way to prevent the file save dialog from popping up, and to prevent the application from closing after they hit the cancel button? Thanks, C_Ascheman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing Form and prevention
This kind of code is related to a single workbook. It'll fire whenever the user
is closing that workbook--not just when the user clicks on the X to close the application. I don't think that there's anything you can do that only fires when the user clicks on the X to close the application. C_Ascheman wrote: Here is the code I have: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.EnableEvents = False MsgBox "You have clicked the X in the upper right corner!" MsgBox "This will close the Excel Program, and all subsequent workbooks opened within." MsgBox "If this was an accident, or you do not want to close all the workbooks then click on the CANCEL button." MsgBox "Otherwise click the OK button, and everything will be closed.", vbOKCancel If vbOK = 1 Then Exit Sub If vbOK = 0 Then Application.Quit End If End Sub This partially does what I am wanting it to do. The purpose of this is if one of the users has multiple workbooks open, and accidentally hits one of the x's in the corner that it gives them a message of what they did. Then Depending whether they hit cancel or ok decides the outcome. If they didn't mean to hit the x, and want to keep the form open then they hit the cancel button. If they do want to close all of it down then they hit ok. The ok part works as intended obviously. The problem is if they hit the cancel button they get the save dialog box. If they hit yes they save it. If they hit no even though they don't want to close the application it does anyway. With cancel all remains open. Is there a way to prevent the file save dialog from popping up, and to prevent the application from closing after they hit the cancel button? Thanks, C_Ascheman -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing Form and prevention
Any suggestions then. Personally I don't think it needs in there, but my boss
disagrees as he has accidentally closed down multiple workbooks by accidentally clicking the x of the excel window. I will be putting this code into all the Excel programs we use, which will be a nuisance, but I don't have much of an option there. Got to do what the boss wants. If you have any suggestions to help me achieve what I am trying to do then please help. Thanks, C_Ascheman "Dave Peterson" wrote: This kind of code is related to a single workbook. It'll fire whenever the user is closing that workbook--not just when the user clicks on the X to close the application. I don't think that there's anything you can do that only fires when the user clicks on the X to close the application. C_Ascheman wrote: Here is the code I have: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.EnableEvents = False MsgBox "You have clicked the X in the upper right corner!" MsgBox "This will close the Excel Program, and all subsequent workbooks opened within." MsgBox "If this was an accident, or you do not want to close all the workbooks then click on the CANCEL button." MsgBox "Otherwise click the OK button, and everything will be closed.", vbOKCancel If vbOK = 1 Then Exit Sub If vbOK = 0 Then Application.Quit End If End Sub This partially does what I am wanting it to do. The purpose of this is if one of the users has multiple workbooks open, and accidentally hits one of the x's in the corner that it gives them a message of what they did. Then Depending whether they hit cancel or ok decides the outcome. If they didn't mean to hit the x, and want to keep the form open then they hit the cancel button. If they do want to close all of it down then they hit ok. The ok part works as intended obviously. The problem is if they hit the cancel button they get the save dialog box. If they hit yes they save it. If they hit no even though they don't want to close the application it does anyway. With cancel all remains open. Is there a way to prevent the file save dialog from popping up, and to prevent the application from closing after they hit the cancel button? Thanks, C_Ascheman -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing Form and prevention
Leave it in there. Everytime they try to close a workbook, they will get the
prompt. After a while, they will ask you to take it out. You had some errors in your code. You test if vbOK = 1 then vbOK will always equal 1 - it is a constant. YOu want to examine the response the user has given. Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.EnableEvents = False MsgBox "You have clicked the X in the upper right corner!" MsgBox "This will close the Excel Program, and all subsequent workbooks opened within." MsgBox "If this was an accident, or you do not want to close all the workbooks then click on the CANCEL button." ans = MsgBox( "Otherwise click the OK button, and everything will be closed.", vbOKCancel) If ans = vbOK Then Exit Sub else cancel = True End If End Sub -- Regards, Tom Ogilvy "C_Ascheman" wrote: Any suggestions then. Personally I don't think it needs in there, but my boss disagrees as he has accidentally closed down multiple workbooks by accidentally clicking the x of the excel window. I will be putting this code into all the Excel programs we use, which will be a nuisance, but I don't have much of an option there. Got to do what the boss wants. If you have any suggestions to help me achieve what I am trying to do then please help. Thanks, C_Ascheman "Dave Peterson" wrote: This kind of code is related to a single workbook. It'll fire whenever the user is closing that workbook--not just when the user clicks on the X to close the application. I don't think that there's anything you can do that only fires when the user clicks on the X to close the application. C_Ascheman wrote: Here is the code I have: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.EnableEvents = False MsgBox "You have clicked the X in the upper right corner!" MsgBox "This will close the Excel Program, and all subsequent workbooks opened within." MsgBox "If this was an accident, or you do not want to close all the workbooks then click on the CANCEL button." MsgBox "Otherwise click the OK button, and everything will be closed.", vbOKCancel If vbOK = 1 Then Exit Sub If vbOK = 0 Then Application.Quit End If End Sub This partially does what I am wanting it to do. The purpose of this is if one of the users has multiple workbooks open, and accidentally hits one of the x's in the corner that it gives them a message of what they did. Then Depending whether they hit cancel or ok decides the outcome. If they didn't mean to hit the x, and want to keep the form open then they hit the cancel button. If they do want to close all of it down then they hit ok. The ok part works as intended obviously. The problem is if they hit the cancel button they get the save dialog box. If they hit yes they save it. If they hit no even though they don't want to close the application it does anyway. With cancel all remains open. Is there a way to prevent the file save dialog from popping up, and to prevent the application from closing after they hit the cancel button? Thanks, C_Ascheman -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing a Form | Excel Discussion (Misc queries) | |||
form closing with the X | Excel Programming | |||
closing user form | Excel Programming | |||
HELP! - Closing form | Excel Programming | |||
HELP! - Closing form | Excel Programming |