none
Exit from Before Close RRS feed

  • Question

  • Hi

    I have a procedure that has 3 steps - let's call them Step 1, Step 2 and Step 3 - each of which is initiated by the user pressing a control button.

    After step 1 and 2 have been executed the user will check the data produced before deciding whether or not  to initiate Step 3. In most cases I expect them to select step 3.

    I propose to put in a check in the BEFORECLOSE event to check if step 3 has been initiated and if not ask the user if they meant to exit without initiating step 3. if they did , then I will continue with the close.

    If they did not then I want to exit from the sub and allow the user to trigger Step 3. If I just exit from the sub, however, I think it will just close the workbook. I want to somehow exit but not close. I know that I could trigger step 3 programmatically but I would prefer it if the user actually did it.

    is there anyway this can be done - or can you think of a different way that I can check that step 3 has been run without use the BEFORECLOSE event.

    thank you.

    Peter 

    Monday, February 6, 2017 4:20 PM

Answers

  • Hi py1-

    in the workbook Beforeclose event, setting the cancel=true stops the workbook from closing:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        'setting cancel = true in one big statement...'
        Cancel = (MsgBox("Did you really want to close this w/o doing the crucial step 3?", vbYesNo + vbCritical, "You might be doing something you really don't want to do") = vbNo)
    End Sub
    This goes on 'ThisWorkbook'


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.


    • Edited by MainSleuth Monday, February 6, 2017 4:27 PM
    • Marked as answer by py1 Tuesday, February 7, 2017 4:15 PM
    Monday, February 6, 2017 4:26 PM

All replies

  • Hi py1-

    in the workbook Beforeclose event, setting the cancel=true stops the workbook from closing:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        'setting cancel = true in one big statement...'
        Cancel = (MsgBox("Did you really want to close this w/o doing the crucial step 3?", vbYesNo + vbCritical, "You might be doing something you really don't want to do") = vbNo)
    End Sub
    This goes on 'ThisWorkbook'


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.


    • Edited by MainSleuth Monday, February 6, 2017 4:27 PM
    • Marked as answer by py1 Tuesday, February 7, 2017 4:15 PM
    Monday, February 6, 2017 4:26 PM
  • Hi py1,

    As you said I assume that there are 3 buttons. one for each step.

    user will perform step1 and step2.

    then there is 2 situations can be occur.

    (1) user can perform step3 and then close the workbook.

    (2) user close the workbook without performing step3.

    so if user performs step3 then there is no issue and you can allow the user to close the workbook.

    but if user not perform the step3 and try to close the workbook then you want to stop (don't want to close workbook) and want to asks user whether he wants to perform step3 or not.

    and you want to do this without _Beforeclose event.

    so I think that after performing step2 user need some time to check the produced data and take decision whether he want to perform step 3 or not.

    so what you can do is try to show him a msg box with option yes and no.

    you need to put this code on step2.

    after processing all the code of step2. you can pause the code for some time like 30 seconds or 1 minute. in this time gap user will view the data and take the decision.

    after that you display the messagebox to user with choice yes and no.

    if user click yes then you can directly call the step3 , user does not need to click step3.

    if user clicks no then you can close the workbook without performing step3.

    if you want to go further in this approach then you can display "Cancel" button on Masgbox with "Yes" and "No".

    so if user click cancel then after 20 seconds or whatever time you want it will again display the Msgbox to user with choice yes, no and cancel to perform step3.

    so it will give some extra time to user to view the data.

    if user click the no and you don't want to close workbook then you can exit without closing the workbook.

    and still step3 button is there. so after if user change his mind to perform step 3 then he can click the button and perform the step.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 7, 2017 3:07 AM
    Moderator
  • thanks - good answer
    Tuesday, February 7, 2017 4:15 PM
  • You're welcome.

    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    Tuesday, February 7, 2017 5:48 PM