Having challenges to code command button on MS Excel VBA

Question

I am new to VBA.

I have a worksheet that is protected/locked with a password.

I have a CommandButton that, when clicked loads a UserForm that is used to enter records into the worksheet.

I want it in such a way that when the CommandButton is clicked, it will prompt the user to enter the unprotect password (used to protect/lock the worksheet), before the UserForm loads.

If the user enters the correct unprotect password, then the UserForm loads. If the user enters the incorrect unprotect password, a MsgBox displays and the UserForm will not load.

The problem is when the CommandButton is clicked, the user will be prompted to enter the unprotect password (Excel's Built-In Unprotect dialog), if the user clicks the "Cancel" button, the UserForm still loads. This is not what i want. What i want is: If the "Cancel" button is clicked, the MsgBox displays, and the UserForm does NOT load.

Below is my code for the CommandButton:

Private Sub CommandButton1_Click()
    On Error Resume Next
    ActiveSheet.Unprotect
    If Err <> 0 Then
    MsgBox:
        MsgBox "Incorrect Password. Unlock Failed!"
    Else
        UserForm3.Show
    End If
End Sub

fcsr fcsr · Accepted Answer · 2017-08-02T18:20:28

So here is my code

Userform1 Layout

notes: there's a PasswordChar property in Textbox1, set it to * so that people won't see the password being typed.

Code for your Command Button on the module:

This will show the userform for password verification.

Sub Button1_Click()

UserForm1.Show

End Sub

Code for Userform1

This is where you assign your password to the Password Variable.

Command Button1, this will check if Password in textbox1 is same as password stored. if its the same it unprotects the sheet with additional message and if not the same then it will msgbox "incorrect password"

Command Button2 will be your cancel button, this will hide the userform1

Private Sub CommandButton1_Click()

Password = "yourpassword"

If UserForm1.TextBox1.Value = Password Then
    Worksheets("Sheet2").Unprotect (Password)
    'this is for unprotecting sheets
    'or you can also use it to show Userform2
    MsgBox "Sheet now unprotected or Userform2 now shows"
    UserForm1.Hide
    Userform2.Show

Else
    MsgBox "Incorrect Password"
End If

End Sub

For CommandButton 2

Private Sub CommandButton2_Click()

UserForm1.Hide

End Sub

Having challenges to code command button on MS Excel VBA

1 Answers