Password Protect Workaround: Using Macro to act like WS Protect

**jaredmccullough** · 03-31-2015, 10:08 AM

I have created this simple format to at the moment stimulate the user from gaining access to a certain WS although it can be circumvented pretty easily by them just selecting "Yes":

Private Sub Worksheet_Activate()

 A = MsgBox("PPG Site Administrator", vbYesNo)
 If A = vbNo Then
 Worksheets("HOME").Activate
 End If

All it does is take them to another worksheet if the select "No"

My intentions would be to take this a step further in which I pull up a Userform which has a textbox for a "Password". If they get the password right it gives them access to the worksheet if they get it wrong it does the same maneuver and sends them back to the "Home" worksheet as shown in the previous code.

Any help would be appreciated.

Jared

**skywriter** · 03-31-2015, 11:04 AM

You can certainly do that. One thing to be aware of is if you create a text box you can go to properties and one of your choices is PasswordChar where you can set a character such as ***, so that when someone is typing the password you only see the characters. I think worksheet activate is a good event to use to display the form. If they deactivate the form you could have code run to re-protect the sheet and set the password again.

Here's some sample code:

Private Sub CommandButton1_Click()
    On Error GoTo ErrorHandler
    Worksheets("Sheet1").Unprotect Password:=TextBox1.Text
    UserForm1.Hide
    Exit Sub
    
ErrorHandler:
    If Err.Number = 1004 Then
       MsgBox "Invalid password"
       TextBox1.Text = ""
       TextBox1.SetFocus
    End If
            
End Sub

Private Sub Worksheet_Activate()

    UserForm1.Show
    UserForm1.TextBox1.Text = ""
    UserForm1.TextBox1.SetFocus


End Sub


Private Sub Worksheet_Deactivate()

    Me.Protect "password"

End Sub