Custom 'cancel' on Auto_Close

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows
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:

Code:
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,380
Office Version
  1. 2019
Platform
  1. Windows
Hi,
Use the Workbook_BeforeClose event which has a Cancel Parameter

Try this

Place following codes in your userforms code page

Rich (BB code):
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


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


Hopefully, this will do what you want.
 
Last edited:

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows
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:

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,380
Office Version
  1. 2019
Platform
  1. Windows
Delete Auto_Close code

Dave
 

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,380
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

Code:
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

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,380
Office Version
  1. 2019
Platform
  1. Windows
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

Code:
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

Code:
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

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows
Perfect Dave! Thanks very much. You are a star!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,935
Messages
5,598,950
Members
414,268
Latest member
bluebandersnatch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top