How to upload and download custom form

Question

Where should I put Load and Unload frm1 (name of the user form frm1) and where should I put Me.Show and Me.Hide?

The button (x) inside the user form does not work.

My download and upload are in the Active-X button code on Sheet 1:

 Private Sub cmdb1_Click()
     Load frm1
     Unload frm1
 End Sub

This way my user form is initialized and I can run the code

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

it shows my user form. Now I have a command button in my user form that has code

Private Sub cmdbClose_Click()
    Me.Hide
End Sub

which I use to hide the routine that runs the last line in cmdb1_Click () and the user form is unloaded. It works.

However, when I click the (x) button in my user form, the following error appears

, cmdb1_Click(). UserForm_QueryClose(), . , , , Load Unload, cmdb1_Click().

:

. ShowUserform cmdbClose_Click , CallumDA. :

Private Sub cmdb1_Click()
    Load frm1
    Call ShowUserform
End Sub

Answer 1

, ,

Sub ShowUserform
    frm1.Show
End Sub

Private Sub cmdbClose_Click()
    Unload Me
End Sub 

Answer 2

:

Dim MyDialog As frm1

Set MyDialog = New frm1    'This fires Userform_Initialize

, , :

If Not MyDialog Is Nothing Then
    Unload MyDialog
End If

Show Initialize . Excel .

, cmdbClose_Click, CallumDA ( ). , , . , :

Private Sub cmdbClose_Click()
    Me.Hide
End Sub

, ( ActiveX) :

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

, , "X" , :

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

, "" . , "" :

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

"":

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

:

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

( , , . , .)

Answer 3

Excel. , () , .

, [OK] [], [OK], [] [X] :

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

:

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

(?), , :

Dim MyDialog As frm1

Set MyDialog = New frm1    'This fires Userform_Initialize

Then a similar example is noted above.

All of this is based on the excellent RubberDuck article , which explains the code above in more detail.