Today I’m going to show you a solution for a challenge faced by lots of newbie developers. Here is the challenge. When we create VBA forms, we often create a Close or Cancel button to close the form.
So users can use that button to close the form. Also sometimes we add some code to that Close button to carry out some tasks while closing the form. Below is an example.
Unload Me
Call UpdateReport
ThisWorkbook.Save
End Sub
So this Close button calls a sub procedure called “UpdateReport” and then saves the file while closing the form.
Suppose there is a VBA application which has a dashboard or report. Then the dashboard or report should be updated when the new data entered through the form. But the program doesn't need to update the dashboard/report on every single entry. Instead the program can update the dashboard/report while closing the form. Because the user can view the dashboard/report only after he closes the form. If you develop the program to update them on every entry then the program will have to call the update sub procedure every time when a user enters a data set. This is the same for the file saving function. You don’t need to save the file whenever the user enters a data set. Instead you can save it when the user closes the form.
And sometimes developers create VBA applications in Excel in such a way that users can only interact with forms. Developers might use the worksheets to store the data. But users will be restricted to only interact with forms. In such programs developers use the close button to close the entire workbook.
For these reasons developers create separate buttons to close the form. But then the problem is that some users use the default close (x) button at the top of the VBA form.
If a user clicks that button, the form will be closed. But the tasks in the Close or Cancel button will not be completed. So how do we solve this problem? We can use the Terminate event of the form to solve this. What you should do is place the code (you want to run when closing the form) inside the Terminate event of the form. Then write “Unload Me” in the Close or Cancel button you created. Then when a user clicks the Close or Cancel button program will close the form. It will also trigger the terminate event of the form. Therefore code inside the Terminate event will also be executed. Here is an example.
Unload Me
End Sub
Private Sub UserForm_Terminate()
Call UpdateReport
ThisWorkbook.Save
End Sub
