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

Last Comment

Martin Liss

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Martin Liss

11/20/2019

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

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

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Aaron Greene

11/20/2019

ASKER

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

11/20/2019

Did you try my approach?

Aaron Greene

11/20/2019

ASKER

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.

fblack61

Martin Liss

11/20/2019

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