[VBA] Executing Macros When a UserForm Closes | Using the Terminate Event

2025年12月27日

Introduction

When providing a VBA UserForm as an operational interface, there are cases where automatic cleanup processing is required at the moment the form is closed. Examples include writing input content to a sheet or recording an activity log.

VBA UserForms feature a Terminate event that occurs just before the form is completely closed and discarded from memory. By utilizing this event, specific cleanup or final processing can be reliably executed, triggered by the termination of the form.

This article explains how to use the Terminate event to automatically execute specific processes when a form is closed.

VBA Sample Code: Executing Processing Upon Closing

This macro runs immediately before the UserForm is unloaded, either via the Unload statement or by clicking the “X” button in the upper right corner, and released from memory.

In this example, the closing time of the form is recorded in the last row of Column A on a sheet named “Log.”

UserForm Code (DataForm)

This code is written directly into the UserForm code module.

' Event executed just before the form is discarded from memory
Private Sub UserForm_Terminate()
    ' Call the cleanup process defined in the standard module
    Call CleanUpProcess
End Sub

Standard Module Code (Module1)

It is easier to manage specific processes intended for execution upon closing by placing them in a standard module.

' Cleanup process called when the form ends
Sub CleanUpProcess()
    Dim logSheet As Worksheet
    Dim lastRow As Long
    
    On Error Resume Next
    Set logSheet = ThisWorkbook.Worksheets("Log")
    On Error GoTo 0
    
    If logSheet Is Nothing Then
        ' Create Log sheet if it does not exist
        Set logSheet = ThisWorkbook.Worksheets.Add
        logSheet.Name = "Log"
    End If
    
    ' Get the last row of Column A in the Log sheet
    lastRow = logSheet.Cells(logSheet.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Write the termination log
    logSheet.Cells(lastRow, "A").Value = "Form closed at " & Now()
End Sub

Explanation of the Code

1. Private Sub UserForm_Terminate()

The Terminate event occurs exactly once, just before the form instance is completely released from memory. This event is triggered when the form is closed using the Unload statement or the “X” button.

Note: Simply hiding the form with the .Hide method does not trigger the Terminate event because the form remains in memory.

2. Call CleanUpProcess

The CleanUpProcess macro, located in the standard module, is called from within the Terminate event procedure. Even if the termination process becomes complex, separating the logic into a different module maintains code readability.

3. Logic of the CleanUpProcess Macro

This macro searches for a worksheet named “Log” and creates it if it is missing. It then records the string “Form closed at [Current Date/Time]” in the last row of Column A. This allows for tracking when work within the form was completed.

Difference Between QueryClose and Terminate

It is important to understand the difference between the Terminate event and the QueryClose event.

Event	Description
QueryClose	Occurs immediately before the form attempts to close. It is possible to cancel the closing action.
Terminate	Occurs just before the form is completely closed and removed from memory. The closing action cannot be cancelled.

In summary, use QueryClose for verifying or confirming the closing operation, and use Terminate for final cleanup after the closure is finalized.