Avatar of Aaron Greene
Aaron Greene
Flag 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?
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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
Martin Liss

Did you try my approach?
Aaron Greene

I did.  It kind of morphed into what I posted.  My mind works in mysterious ways.
Your help has saved me hundreds of hours of internet surfing.
Martin Liss

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