How to load and unload a Userform

I recommend that you create an instance of your userform:

Dim MyDialog As frm1

Set MyDialog = New frm1    'This fires Userform_Initialize

You can then easily check whether the form is loaded before attempting to unload it:

If Not MyDialog Is Nothing Then
    Unload MyDialog
End If

I also recommend that you don't call the Show method from the form's Initialize event. Think of your userform as just another object in Excel and manage it from your main code body.

Also, I wouldn't unload it in the cmdbClose_Click event as suggested by CallumDA (though that works fine to solve the current issue). Often you will need to be able to refer to values on your form from your main code body, and if you unload it they won't be available. Instead, keep it like you had it in the first place:

Private Sub cmdbClose_Click()
    Me.Hide
End Sub

So your main code body (in your activeX button) will look something like this:

Dim MyDialog as frm1

Set MyDialog = New frm1      'This fires Userform_Initialize
'Place any code you want to execute between the Initialize and Activate events of the form here
MyDialog.Show           'This fires Userform_Activate
'When the close button is clicked, execution will resume on the next line:
SomeVariable = MyDialog.SomeControl.Value
'etc.

If Not MyDialog Is Nothing Then
    Unload MyDialog
End If

You can also catch the event that fires when a user clicks the "X" on the form, and prevent the form from being unloaded:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
        Me.Hide
    End If
End Sub

Lastly, often you need a Cancel button on the form. The way I handle this is to create a "Cancelled" property in the form's code-behind:

Public Cancelled as Boolean
'(Note You can create additional properties to store other values from the form.)

In the Cancel button's click event:

Private Sub cmdbCancel_Click()
    Me.Cancelled = True
    Me.Hide
End Sub

And in the main code body:

Dim MyDialog as frm1

Set MyDialog = New frm1
MyDialog.Show

If Not MyDialog.Cancelled Then
    SomeVariable = MyDialog.SomeControl.Value
    SomeOtherVariable = MyDialog.SomeOtherProperty
    'etc.
End If

If Not MyDialog Is Nothing Then
    Unload MyDialog
End If

(I know the above is not strictly the correct way to declare a property, but this will work fine. You can make it read-only in the usual way if you wish.)


I struggled for years with forms in Excel. I could never understand how an object (a form) could be destroyed but still have it's code running.

I now use this code on every form that has [OK] and [Cancel] buttons, as it allows you to control the [OK], [Cancel] and [X] being clicked by the user, in the main code:

Option Explicit
Private cancelled As Boolean

Public Property Get IsCancelled() As Boolean
    IsCancelled = cancelled
End Property

Private Sub OkButton_Click()
    Hide
End Sub

Private Sub CancelButton_Click()
    OnCancel
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
        OnCancel
    End If
End Sub

Private Sub OnCancel()
    cancelled = True
    Hide
End Sub

You can then use the form as an instance as follows:

With New frm1    
    .Show
    If Not .IsCancelled Then
        ' do your stuff ...
    End If
End With

or you can use it as an object (variable?) as noted above such as:

Dim MyDialog As frm1

Set MyDialog = New frm1    'This fires Userform_Initialize

Then similar instance noted above.

This is all based on an excellent article by RubberDuck which goes into more detail and explains the code given above.


Put this in a standard module and link it up to the button you use to show the userform

Sub ShowUserform
    frm1.Show
End Sub

And then in the userform

Private Sub cmdbClose_Click()
    Unload Me
End Sub 

Tags:

Excel

Vba