Password Protect Viewing of Individual Worksheets?

[BKelleher]

PLEASE HELP.

I have a file w/ individuals monthly sales information (per worksheet) that needs to be distributed in one mass email to the entire sales force. I want everyone to have access to the "Summary" tab, but individuals should only have access to their own "Details" tab. I know there has to be a way to accomplish via VBA. However, I'm not proficient in writing code. I'd really appreciate any guidance/suggestions.

Thanks!

 

[lenze]

How many sheets are you talking about?
lenze

 

[BKelleher]

roughly 30.

 

[sykes]

If the users are all on a network, then you could use their usernames to access their individual sheets.
You could hide all sheets properly by making them "Very hidden", then just make visible the "Summary" sheet, and the sheet bearing their network username.

Private Sub Workbook_Open()
Dim sht As Worksheet

For Each sht In ThisWorkbook.Sheets
    If sht.Name <> "Summary" Then
sht.Visible = xlSheetVeryHidden
End If
Next
Sheets(Environ("UserName")).Visible = True
End Sub

Using this method, you'd have to change the individuals' sheet names to that of their network log-on username, so this may not be possible for you, but it would probably make for the simplest solution.

Otherwise, perhaps you'd have to have a lookup table on a seperate sheet in the workbook, which was also "Very" hidden, and use that to access their sheet via a password prompt when the workbook opened, which wouldn't be too difficult to achieve.

 

[BKelleher]

unfortunately, they're not all on the same network and i need a set up so that each worksheet has its own password -- except the summary, which wouldn't require a password. thanks!

 

[lenze]

A little up front work
You have a sheet named "Summary"
Add a new sheet and Name it "LogIn"
A1 = Name
B1 = Password
C1 = SheetName
Now fill the columns in with the UserNames, their password, and the Sheet Name they have access to. Enter this code

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name <> "Summary" Then ws.Visible = xlSheetVeryHidden
Next ws
End Sub
 
Private Sub Workbook_Open()
Dim user As String
Dim pwd As String
Dim ct As Integer
LR = Sheets("LogIn").Cells(Rows.Count, "A").End(xlUp).Row
user = InputBox("Enter your UserName")
Set C = Worksheets("LogIn").Range("$A1:$A" & LR).Find(user, LookIn:=xlValues)
    If C Is Nothing Then
        MsgBox "Unauthorized to proceed"
        Me.Close
    End If
ct = 2
retry:
    pwd = InputBox("Enter Password")
    If pwd <> Sheets("LogIn").Cells(C.Row, 2) Then
    If ct = 0 Then
       MsgBox "Out of tries"
       Me.Close
   End If
       MsgBox "Wrong Password." & Chr(10) & "You have " & ct & " tries left"
       ct = ct - 1
       GoTo retry
   End If
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name = Sheets("LogIn").Cells(C.Row, "C") Then ws.Visible = xlSheetVisible
Next ws
End Sub

Now Save the File. When reopened, they will enter their Name and Password and their sheet will be unhidden. You will have to get a UserName and Password for each one. Not you will also need a macro for you to unhide the LogIn sheet for maintenance! Try this on a copy and when you send it send a copy!!

HTH
lenze

 

[lenze]

BTW, you may need to remove the Option Explicit or Dim C as Range. I forgot that
lenze

 

[BKelleher]

hmmm. it's not working. not sure if I follow your last comment. thanks!

 

[lenze]

How is it NOT working? It does for me!!!
Are you getting an Error message? My last comment had to do with the line
"Option Explicit" because I forgot to declare "C" which I used in the code. So, you can remove "Option Explicit" or add "Dim C as Range". Otherwise, you will get an error

lenze

lenze

 

[BKelleher]

still not working. what should the code look like w/ the Option Explict/Dim C as Range change? I don't understand what you're talking about here... Thanks!