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!

 

[lyjoyce]

Thanks a lot, ndendrinos. will try to work that out.

 

[jputman]

Small addition... there is currently nothing stopping a user from easily just going into the VBE once they have successfully opened their sheet and changing other worksheets Visible properties manually and viewing their Worksheets too... which sounds like could be a security hole that you want to avoid.

To make sure this does not happen, make sure to password protect your VBA project (Tools=>VBAProject Properties=>Protection tab from the VBE)!

 

[dado6612]

AWESOMEEEEEEEEEE ray:

 

[psamu]

Ive done that before, but I can't exactly remember what I did. I can think of two approaches. One would be to duplicate the Name in Column "A" and PAassword in Column "B" of youir sheet for each Sheet to be visible and then use FINDNEXT (See VBA help files)
HTH
lenze

Everything works fine, but I tried to type duplicate name in column "A" and same Password Column "B", but did not work, please help. Thanks.

 

[psamu]

The fact you are in 2007 should not matter. Here is the corrected 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
Dim C As Range
Dim LR As Long
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

Sorry, I also missed the LR

lenze

This code is perfectly working, but if I want to show two or more sheets to the user what can be done?
Thanks

 

[pschill2]

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

Tried your VBA. not working for me either. I'm using 2007. The two scripts, one for the Summary, the other for LogIn page are successfully hiding the tabs with user id and passwords, but no prompt for the user ID and password