Triggering procedures before User Form closes

Aaron Greene
Aaron Greene used Ask the Experts™
on
I have developed a user form in excel, and I want to determine the best way to make sure that the data is saved if the close button is hit.  Currently, I have a save procedure in the UserForm_QueryClose event and also in the UserForm_Deactivate event.  I am thinking that I should put the same procedure in the Workbook_BeforeClose event.  Would the form events occur before the workbook events?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I think the the best thing to do would be to put something like
Dim gbClose As Boolean

Open in new window

in a module. Then set it to True when you open the userform and set it to False when the save is done. Then do something like this
Private SubWorkbook_BeforeClose
    If gbClose Then
        ' Run your "save" procedure
    End If
End Sub

Open in new window

Aaron GreeneProgrammer

Author

Commented:
I realize this could have been done in a single step, but I have added these lines to check if the user form ddDataEntry is visible and stop the workbook from closing if it is.  I have the save procedures tied to the the QueryClose event of the form.  This should prevent most accidental closures before the data is saved.

Public Function returnsDataEntryFormLoaded() As Boolean
returnsDataEntryFormLoaded = ddDataEntry.Visible
End Function

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim fDataEntryLoaded As Boolean
fDataEntryLoaded = basFunctions.returnsDataEntryFormLoaded
Select Case fDataEntryLoaded
Case True
Cancel = True
Exit Sub
Case False
End Select
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Occurs before a userform is closed or unloaded.
Call basProcedures.executeSaveFormDataToWksht
Call basProcedures.executeSaveWkshtData
Call basProcedures.executeSaveAndQuit
End Sub
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Did you try my approach?
Aaron GreeneProgrammer

Author

Commented:
I did.  It kind of morphed into what I posted.  My mind works in mysterious ways.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you including a new one concerning your Experts Exchange rank.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial