Custom 'cancel' on Auto_Close

[RobbieC]

Hi there, I have a 'very customised' spreadsheet which I have replaced all alert boxes with customised userforms.

I have a form called CloseAreYouSure which is triggered when Auto_Close kicks in. This just confirms whether or not the application is to close down with a 'yes' & 'no' button:

Sub Auto_Close()


CancelAreYouSure.Show
If CancelForm = False Then
    Exit Sub
End If

Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

If the user chooses 'yes', the sub continues and the program shuts down without the final prompt 'save', don't save' or 'cancel'

But if the user chooses 'no' the Exit Sub doesn't kick in and instead the built-in 'save', don't save' or 'cancel' form appears...

Is there a way of cancelling the Auto_Close without the second 'alert' showing? Or is there a better way to do this?

If you can point me in the right direction, I'd be very grateful

Thanks

Rob

 

[dmt32]

Hi,
Use the Workbook_BeforeClose event which has a Cancel Parameter

Try this

Place following codes in your userforms code page

Function Response() As VbMsgBoxResult
    Me.Show
    Response = Val(Me.Tag)
    Unload Me
End Function


Private Sub btnNo_Click()
    Me.Tag = vbNo
    Me.Hide
End Sub


Private Sub btnYes_Click()
    Me.Tag = vbYes
    Me.Hide
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = CBool(CloseMode = 0)
    If Cancel Then
    Me.Hide
    Me.Tag = vbYes
    End If
End Sub

Change your Yes No Button names shown in RED as required


Place Following in the Thisworkbook Code Page

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Cancel = CBool(CancelAreYouSure.Response = vbYes)
        ThisWorkbook.Saved = Cancel
End Sub

Hopefully, this will do what you want.

 

[RobbieC]

dmt, that's brilliant! (I swapped the vbYes and vbNo around the other way as it was working backwards. I also renamed the userform 'ExitForm')

When I click on 'NO' it cancels the 'exit', however, when I click on 'YES', it brings up the form a second time and you have to click 'YES' again for the program to close...

I have uploaded a test sheet here to show what I mean: ExitTest.xlsm

The Auto_Close sub only has this code:

Sub Auto_Close()

Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

And the rest of the code is as you wrote. Why would this bring up the for twice?

Cheers mate, thanks for your help

Rob

 

[dmt32]

Delete Auto_Close code

Dave

 

[RobbieC]

hrm. When I remove the Auto_Close code, the Alert Box re-appears

 

[dmt32]

hrm. When I remove the Auto_Close code, the Alert Box re-appears

You said in an earlier post that you had swapped vbYes & vbNo around as it was working backwards?

Perhaps help if you can clarify what the required sequence of events is.

Dave

 

[RobbieC]

Sure thing. When the user closes the program using the 'X' in the top right of the screen, I don't want the conventional 'Save', 'Dont Save' or 'Cancel' alert box to show.

Instead a userform (ExitForm) appears and asks "Are you sure you want to close?". If the user clicks 'No', it cancels the Exit. If they click 'Yes', it closes down the program (so it's just the wording of the userform that means the yes/no is reversed from your code (Are you sure you want to Cancel) if you get my drift.

My original Auto_Close code had extra code in there to save the program and settings on exit, but the main thing is that the final alert box didn't show with the use of:

Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ActiveWorkbook.Close
Application.DisplayAlerts = True

My code before worked fine, but it didn't give the user the opportunity to cancel the 'Exit of Program' - that's all I want to do.

The spreadsheet is actually a compiled .exe file (MyProgram.exe), although it runs exactly as a normal excel file, but the final Exit Alert Box prompts the user to save the file and it thinks it is MyProgram.xlsm, which it is not. This is why I can't use the Alert Box built into Excel.

Thanks for your time Dave

 

[RobbieC]

The example workbook that I uploaded shows the code in action: ExitTest.xlsm - if the Auto_Close code is removed, the Alert Box shows up, but if the code is in place, the custom userform appears twice and the second time, either button closes the program...

 

[dmt32]

Sure thing. When the user closes the program using the 'X' in the top right of the screen, I don't want the conventional 'Save', 'Dont Save' or 'Cancel' alert box to show.

If the user clicks 'No', it cancels the Exit.

If they click 'Yes', it closes down the program

This is why I can't use the Alert Box built into Excel.

1 - reverse the changes you made to the Yes No Buttons as shown below

Private Sub btnNo_Click()
    Me.Tag = vbNo
    Me.Hide
End Sub




Private Sub btnYes_Click()
    Me.Tag = vbYes
    Me.Hide
End Sub

2 - Replace Before Close code with updated version below

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Cancel = CBool(ExitForm.Response = vbNo)
        ThisWorkbook.Saved = Not Cancel
End Sub

and see if this now does what you want


Dave

 

[RobbieC]

Perfect Dave! Thanks very much. You are a star!