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