CODEWITHSUNDEEP
excelvbams-worduserform
Malachilee
Malachilee

Reputation: 99

How to automatically save workbook when userform is closed? From the red "X"

How can I automatically save the hidden excel workbook when the userform is close? The userform is populating the workbook with data that is also populating comboboxes on the userform. I need to save the workbook so any data added is not lost when the userform is closed.

I'd like the user to input data into the userform and when they are done the close the userform and all data will be saved into the workbook for future use.The workbook is hidden so it's not possible to save the workbook.

Screen Shot

Upvotes: 1

Views: 1111

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

Handle the QueryClose event. When the CloseMode parameter is vbFormControlMenu, the form was closed by a user clicking the red "X" button.

The Terminate event is a special lifetime event (along with Initialize) that runs once in the lifetime of the object, when it is being destroyed -- regardless of how the form was closed. Depending on how you're showing your form, that handler may end up executing at unexpected times - more specifically, if you're showing the form's default instance, you are not controlling the form object's lifetime, so using the Terminate handler for this, is a bad idea.

See UserForm1.Show for a (much) deeper dive on user forms in VBA.

Upvotes: 4

Related Questions