Execute code when form is closed in VBA (Excel 2007)

Question

I would like to execute some code when a user closes a form using the x button in the top right corner of the window (I have the form load when the excel spreadsheet is opened, and it hides Excel. I want to exit excel once the form is closed, or at least show excel again so the user may exit it manually)

Looking at the form properties, the Unload property is not present, nor am I able to figure out how to make a function which executes when the form is closed.

Unfortunately, coding this in VB is not an option, it must be VBA.

I'm aware of the code needed to unhide Excel or quit it outright, just not how to tie it to the unload event.

Answer 1

You can use QueryClose event of the UserForm as follows:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        ' Your codes
        ' Tip: If you want to prevent closing UserForm by Close (×) button in the right-top corner of the UserForm, just uncomment the following line:
        ' Cancel = True
    End If
End Sub

You can also use vbFormControlMenu like this:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        'Your code goes here
    End If
End Sub

Answer 2

A colleague was able to provide the answer, including example here for everybody else

Private Sub userform_terminate()

    'Code goes here

End Sub

Answer 3

You can use Unload Me in VBA to close a form. Just put the code to close Excel immediately following that.

Answer 4

Private Sub Form_Unload(Cancel As Integer)
    Dim msgRes As VbMsgBoxResult
    msgRes = MsgBox("Exit form ?", vbYesNo)
    If msgRes = vbYes Then
        'optional code
    ElseIf msgRes = vbNo Then
        Cancel = True
    End If
End Sub

Answer 5

I was able to prevent the form from closing when the X button was click using the following:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
   Cancel = MsgBox("Please confirm cancellation", vbOKCancel + vbQuestion) = vbCancel
End Sub