Link to home
Start Free TrialLog in
Avatar of Aaron Greene
Aaron GreeneFlag for United States of America

asked on

Triggering procedures before User Form closes

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?
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aaron Greene


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
Did you try my approach?
I did.  It kind of morphed into what I posted.  My mind works in mysterious ways.
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