Password on a Command button

[Fleurie]

Is it possible to put a password on a command button such that it displays a message to say that a password is needed to go to a particular worksheet, in the same workbook?

Can this be done by recording a macro or can it only be done through VBA code?

I would be grateful for any help.

Fleurie

 

[VBAjedi]

Hi!

There's a number of solutions to this - many have been discussed in previous threads in the VBA forum. Here's a VBA routine I use to allow access to two sheets ("DATA" and "Admin&quot. I store the password on the hidden sheet, but you could also just put it right in the code. Put this in the _click event of your button (double-click the button in design mode) and edit it to use your sheet names:

Dim Password
Dim verify, attempts
attempts = 0
GetPassword:
Password = InputBox("Please enter the Administrator password")
If Password = Sheets("Admin").Range("B1").Value Then
    Sheets("DATA").Visible = True
    Sheets("Admin").Visible = True
    Sheets("Admin").Activate
Else
    attempts = attempts + 1
    If attempts > 2 Then
        MsgBox ("Incorrect password. Deleting all data from worksheet now. (Just kidding!)")
        Exit Sub
    End If
    verify = MsgBox("Incorrect password. Try again?", vbOKCancel)
    If verify = 2 Then Exit Sub
    GoTo GetPassword:
End If

To make this work, you also need to hide the sheets using a special command ("xlVeryHidden&quot. Sheets hidden this way can only be un-hidden from the code - the Excel menu "Unhide Sheets" won't work. So it's pretty safe unless a user knows how to get into your code (and then not much of anything is safe anyway!). I put the following code in the ThisWorkbook object:

Private Sub Workbook_Open()
    Run ("SecureWorkbook")
End Sub

and this code into a Module:

Sub SecureWorkbook()
    Sheets("DATA").Visible = xlVeryHidden
    Sheets("Admin").Visible = xlVeryHidden
End Sub

Now every time the workbook is opened, the two sheets are re-hidden. You could also trigger the SecureWorkbook sub from a button on the hidden sheets to rehide them when the user is finished.

Hope this helps you! If you are very new to VBA code, feel free to ask for clarification.

VBAjedi