Sub Auto_Close() - prompting on exiting workbook...

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
371
Office Version
  1. 2010
Platform
  1. Windows
Hi there, I have a workbook where I am exporting 'data inputted' instead of saving the workbook. I have all of this working fine apart from when a user closes the workbook. Usually, you'll get a prompt "Workbook not saved? Save Workbook?" with a yes/no/cancel option.

However I'm trying to save my 'data' instead of the 'workbook'. I can hijack the autoclose and launch my own userform with:
Code:
Sub Auto_Close()
     SaveOnExit.Show
     ThisWorkbook.Saved = True
End Sub
and on my userform 'SaveOnExit' I have 3 buttons - Save, Don't Save & Cancel

The Save and Don't Save buttons are not a problem because when I unload the userform, the Auto_Close() macro continues and the workbook closes, however I need to get the cancel button to exit the Auto_Close routine...

I have searched for a solution, but can't find anything... Is there a way to do this, or am I barking up the wrong tree with this method?

If you can point me in the right direction, that'd be brilliant. Thanks
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,974
For the CANCEL button try "Exit Sub".

If that doesn't work, for the CANCEL button have the user directed to a different macro that takes them back to an origination point.

You might also try putting the code for CANCEl button first and the other parts of the macro after it.
 

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
371
Office Version
  1. 2010
Platform
  1. Windows
Unfortunately, Exit Sub doesn't work... I think that it just exits the CancelButton_Click Sub not the Auto_Close() sub underneath that has triggered the Userform

I think that it is the Sub Auto_Close() routine.... Once it has begun, I can't seem to stop it :(

When you say "
have the user directed to a different macro that takes them back to an origination point"... I don't really understand
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,974
Code:
Option Explicit


Function MsgYesNo() As Long
    MsgYesNo = MsgBox("Press Yes[Save & Close] - No[No Save & Close} - Cancel", vbYesNoCancel + vbDefaultButton1, "Yes No Cancel")
End Function


Sub MsgYesNoSub()
    Select Case MsgYesNo
    Case vbYes
        MsgBox "Yes selected"               '<--- Remove this before use.
        Application.DisplayAlerts = False
        ThisWorkbook.Save
        Application.Quit
        ThisWorkbook.Close
    Case vbNo
        MsgBox "No selected"                '<--- Remove this before use.
        Application.DisplayAlerts = False
        Application.Quit
        ThisWorkbook.Close
    Case vbCancel
        MsgBox "Cancel selected"            '<--- Remove this before use.
        Exit Sub
    End Select
End Sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,489
Office Version
  1. 2019
Platform
  1. Windows
Auto_Close event is for backwards compatibility & should really only be used for users of earlier excel versions.
You should use the Workbook_BeforeClose event instead which has the Cancel argument available to manage requirements such as yours.

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,127,991
Messages
5,628,007
Members
416,286
Latest member
ko15

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