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

 

[sykes]

OK
Always make a copy of the original workbook before starting, and test out the method on the copy fully to ensure you're happy with the results, before committing to the original.

Be sure to remove any of the code we mentioned earlier, if you tried it.
Go into the VBA project manager (from your w'book you can use Alt+F11).
Find your project in the L/H pane.
Double click the "This Workbook" module.
With the drop-down boxes on the RHS, select "Workbook" from the LH one, and "Open" from the RH one.
You should see this:

Private Sub Workbook_Open()

End Sub

This is where any code goes, which you want to automatically run when a user opens the workbook.
Copy the following, and paste in between the two lines you saw above:

Dim sht As Worksheet
Dim txt As String
    For Each sht In ThisWorkbook.Sheets
        If sht.Name <> "Sheet1" Then
            sht.Visible = xlSheetVeryHidden
        End If
    Next

txt = InputBox("Your password please", "PASSWORD REQUIRED")

Select Case txt
    Case "pword2"
        With Sheets("Sheet2")
            .Visible = True
            .Activate
        End With
        
    Case "pword3"
        With Sheets("Sheet3")
            .Visible = True
            .Activate
        End With
    
    Case "pword4"
        With Sheets("Sheet4")
            .Visible = True
            .Activate
        End With
    Case Else
        Me.Close (False): Exit Sub
End Select

The whole lot should look thus:

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

txt = InputBox("Your password please", "PASSWORD REQUIRED")

Select Case txt
    Case "pword2"
        With Sheets("Sheet2")
            .Visible = True
            .Activate
        End With
        
    Case "pword3"
        With Sheets("Sheet3")
            .Visible = True
            .Activate
        End With
    
    Case "pword4"
        With Sheets("Sheet4")
            .Visible = True
            .Activate
        End With
    Case Else
        Me.Close (False): Exit Sub
End Select
End Sub

In the lines ............Case "pword2" (for example), change the "pword2" to that of your manager's password who's to use sheet2 - be sure to keep the double quotation marks.
Do the same for the other Case...passwords i.e. for sheets 3 and 4.


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.
Save the workbook.
Close it, then re-open it.

 

[gmazza76]

Morning,

Sorry about this i have been asked to amend the password issues to a button.
I have used the following code to do a pop up with a box for user name / password.

If they are identified as being correct i would like it 2 unhide say Sheet3 & Sheet4. But as i have 8 people using the workbook it needs to be shared which i can do put i will need 2 people passwords to open the same info then next 2 another set and so on. Is there any way i can do this as i am unsure how to set differnet outcomes for different passwords.

I am using the code below to check username/passwords

If Worksheets("Info").Range("I" & cmbMgrs.ListIndex + 2) = txtPass Then
MsgBox "Correct password entered, please wait while open Car Parks are Checked", vbInformation
 
'Then I need it to unhide ("Sheet1,Sheet2")

Hopefully you can point me in the right direction
Gavin

 

[sykes]

Hi Gavin

You really must accurately identify what you need from the outset, as folk volunteer a great deal of their time helping others out. If you (or your boss) keep moving the goalposts, it might be better to pay for a commercial solution, then you can change things around as much as you like.
I think my latest solution is a very good one, and with some thought, you'll be able to adapt it for your most recent requirements.

All the best, and good luck.