Command Still Executes Despite Cancel Button

Question

I have a button the executes a user form to display with a required password before executing the buttons command, however when the user form is displaying it freezes the rest of the sheet. On the user Form a I created a cancel button so that the user can exit out of the UserForm should he not know the password and use the other buttons I have on the sheet. When the user clicks the cancel button, it still executes the command even though he/she did not put a password in. The user Form works properly when you enter the correct password/incorrect password, its only when you click cancel that it does not work. Could any on please offer any help? see my code below for the button and the code for my cancel button

Sub Feeder_Schedule()

UserForm1.Show

If Sheets("Bulk_Data").Visible = xlVeryHidden Then
    Sheets("Bulk_Data").Visible = True
    End If

Sheets("Bulk_Data").Select

Sheets("Home").Visible = xlVeryHidden


End Sub

code for the Cancel button

Private Sub CommandButton1_Click()

Unload Me


End Sub

I do not see any code where you check if the user clicked ok or cancel or whatsoever. The code after/below Userform1.show will run as soon as you dismiss the form. And never ever put an Unload into a userform. — Storax

Storax Storax · Accepted Answer · 2018-03-02T21:09:55

If you want to make it right change/add to your code of the userform

Option Explicit
' USERFORM CODE
Private m_Cancelled As Boolean

' Returns the cancelled value to the calling procedure
Public Property Get Cancelled() As Variant
    Cancelled = m_Cancelled
End Property

Private Sub buttonCancel_Click()
    ' Hide the Userform and set cancelled to true
    Hide
    m_Cancelled = True
End Sub


' Handle user clicking on the X button
Private Sub UserForm_QueryClose(Cancel As Integer _
                                  , CloseMode As Integer)

    ' Prevent the form being unloaded
    If CloseMode = vbFormControlMenu Then Cancel = True

    ' Hide the Userform and set cancelled to true
    Hide
    m_Cancelled = True

End Sub

Your code could then look like that

Sub Feeder_Schedule()

Dim frm As UserForm1
    Set frm = New UserForm1

    frm.Show

    If Not frm.Cancelled Then

        If Sheets("Bulk_Data").Visible = xlVeryHidden Then
            Sheets("Bulk_Data").Visible = True
        End If

        Sheets("Bulk_Data").Select

        Sheets("Home").Visible = xlVeryHidden

    End If

End Sub

Command Still Executes Despite Cancel Button

1 Answers