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

[RobbieC]

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:

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

 

[Logit]

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]

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]

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]

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