How to load and unload a Userform

Question

Where should I put Load and Unload frm1 (Userform name is frm1) and where should I put Me.Show and Me.Hide?

The (x) button within the UserForm doesn't work.

My Load and Unload is within the Active-X command button code on Sheet1:

 Private Sub cmdb1_Click()
     Load frm1
     Unload frm1
 End Sub

This way my UserForm is initialized and I can run the code

Private Sub Userform_Initialize()
    'Some other code that Works...  
    frm1.Show
End Sub

that shows my Userform. Now I have a command button in my Userform that has the code

Private Sub cmdbClose_Click()
    Me.Hide
End Sub

which I use to hide the sub, upon which the last line within cmdb1_Click() is executed and UserForm is unloaded. This Works.

However when I press the (x) button in my UserForm, the following error appears

Debugger says error lies within cmdb1_Click(). I've tried adding a sub called UserForm_QueryClose(), but the error persists. If I'd have to guess, I'd say the error is caused by the way I handle Load and Unload, thus by cmdb1_Click().

EDIT:

My problem is solved. ShowUserform and cmdbClose_Click contain the code CallumDA suggests. My command button now has:

Private Sub cmdb1_Click()
    Load frm1
    Call ShowUserform
End Sub

Answer 1

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 

Answer 2

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.)