Worksheet Passwords

[gmazza76]

Good Morning,

I have a workbook with 4 sheets in.

Sheet 1 - Is an input screen asking various questions relating to the job a person does.

Sheet 2 - 4 are the sheets that hold the information depending on what the manager/agent enters into sheet1.

I have my macro saving the info based on managers name, so is there anyway i can password each of the other sheets so that they are visible but when the tab is clicked it asks for a password before it can be viewed?

Many thanks
GAvin

 

[ColinKJ]

Hi gmazza76,

This assumes that the Users do not have access to the code, as the Password has to be hard coded. This code edited accordingly would be needed in each sheet to be protected This example assumes that Sheet 1 is the sheet visible to all and Sheet 2 the sheet being accessed.

Private Sub Worksheet_Activate()
Sheets(2).Visible = 0
a = InputBox("Enter Password")
If a <> "colin" Then
Sheets(1).Activate
Sheets(2).Visible = -1
Else
Sheets(2).Visible = -1
Application.EnableEvents = False
Sheets(2).Activate
Application.EnableEvents = True
End If
End Sub

 

[ColinKJ]

gmazza76,

It should also be noted, that if Macros are disabled, the Sheets would all be accessible.

However, if you hide all Tabs before you save, and Password protect your Macro project, the following code,will only make the Tabs visible if Macros are enabled. If Macros are disabled, the Tabs will not be accessible.

Private Sub Workbook_Open()
For a = 2 To Sheets.Count
Sheets(a).Visible = -1
Next a
End Sub

 

[sykes]

You could use the sheets' _activate events for this - to only display the required sheet, but I've found that by holding down the mouse on the sheet's name tab, the sheet can be viewed until releasing the mouse click again, when the _activate code finally fires - so that method's not particularly secure (not that anything's really secure in Office............)

I suggest setting the visibility of all but sheet1, to "XLVeryHidden", each time Sheet1's selected, in order to properly hide the other sheets.
To achieve this, put the following into Sheet1's _Activate event:

Private Sub Worksheet_Activate()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets
    If sht.Name <> "Sheet1" Then
        sht.Visible = xlSheetVeryHidden
    End If
Next

Then, in design mode, put 3 ActiveX Option buttons onto sheet 1, perhaps within a group box with some text explaining to click the option button to see the sheet etc etc.
Make the caption of the first "Select Sheet2", that of the second "Select Sheet3", and that of the third "Select Sheet4"

Finally, code the 3 buttons' _click events thus:

Private Sub OptionButton1_Click()
Dim txt As String
txt = InputBox("Password for sheet2, please", "PASSWORD REQUIRED")
If txt = "pword2" Then
    With Sheets("Sheet2")
        .Visible = True
        .Activate
    End With
End If

End Sub

Private Sub OptionButton2_Click()
Dim txt As String
txt = InputBox("Password for sheet2, please", "PASSWORD REQUIRED")
If txt = "pword3" Then
    With Sheets("Sheet3")
        .Visible = True
        .Activate
    End With
End If
End Sub

Private Sub OptionButton3_Click()
Dim txt As String
txt = InputBox("Password for sheet2, please", "PASSWORD REQUIRED")
If txt = "pword4" Then
    With Sheets("Sheet4")
        .Visible = True
        .Activate
    End With
End If
End Sub

Take the sheet out of design mode, click into sheet1 (to hide the others) and you should be done.
You could, of course, add a message box to each bit of code, if the user gets the password wrong etc, but I'm sure you can work that out!

NB don't forget to password protect your actual VBA project, otherwise users can get into there, and view the passwords for each sheet.
To protect the project, in the VBA explorer, right-click the project name, select "Project properties", select the "Protection" tab, and lock the project, along with entering a password.

 

[sykes]

Oh bum! Just realised that a couple of the message boxes have the wrong prompt (thanks to copy & paste).

The 3 option buttons' code should read thus:

Private Sub OptionButton1_Click()
Dim txt As String
txt = InputBox("Password for sheet2, please", "PASSWORD REQUIRED")
If txt = "pword2" Then
    With Sheets("Sheet2")
        .Visible = True
        .Activate
    End With
End If

End Sub

Private Sub OptionButton2_Click()
Dim txt As String
txt = InputBox("Password for sheet3, please", "PASSWORD REQUIRED")
If txt = "pword3" Then
    With Sheets("Sheet3")
        .Visible = True
        .Activate
    End With
End If
End Sub

Private Sub OptionButton3_Click()
Dim txt As String
txt = InputBox("Password for sheet4, please", "PASSWORD REQUIRED")
If txt = "pword4" Then
    With Sheets("Sheet4")
        .Visible = True
        .Activate
    End With
End If
End Sub

 

[ColinKJ]

Hi gmazza76,

If you want to avoid the Option Buttons let me konw.

 

[sykes]

gmazza76.
Are either of our solutions what you need?

It would be polite to give us some feedback, and it's also useful for other users who may access these solutions in the future.

 

[gmazza76]

Sorry for the delay in posting a reply i have only just been able to get on line.

If possible i would prefer there to be no buttons on "Sheet1" bar the print, submit and clear just so as not to confuse people.

Is there anyway a password can be asked for when the Tab at the bottom of the sheet is pressed.

If not i will use the code you have supplied and buttons if need be.

Cheers
Gav

 

[sykes]

Hi Gav

yes - you can use the worksheet_activate event, but as I said in a previous post, the code doesn't fire until the user takes their mouse click off, so that the sheet and all of it's contents can be viewed by anyone until they release the mouse click - upon which the code will fire and the sheet can be hidden etc etc.
As you need the sheet contents to be only visible to the specific manager, this defeats the object somewhat.

There may be another solution though - you could run some code in the workbook_open event, which asked for a password (different one for each user). Depending upon the password provided, only your sheet1 along with that user's sheet would be visible to them.
Would that do the trick?

 

[gmazza76]

Good Evening,
The last option would be brilliant.
Can you let me know how to do this?

Cheers