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!

 

[BKelleher]

also - I'm in Excel 2007. which might have something to do with this issue...

 

[lenze]

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

 

[bcfisher]

Don't mean to hijack this thread, but is there a way to allow someone to view multiple sheets?

 

[lenze]

Hi Bc:
Yes, I'm sure it can be adjusted to accomodate that. I suggest you start a new thread and refer back to this one for code adjustment. Others are more likely to look a a post with only a few responses than one with 14+. I will look for it tomorrow or Monday.

lenze

 

[lyjoyce]

The whole post is exactly what I have been looking for, but I have absolutely no clue about codes. You mentioned about the need of formulating a macro. So, do you mind showing me how to write that macro? Many many thanks.

 

[Excelestial]

I publish an expense report monthly which is privy only to the spenders. I have a macro set up such that I select a name from a dropdown...the sheet calculates the expenses for the individual I selected, it then extracts and emails to that person.

Cookie crumbs. lol

 

[lyjoyce]

I am still in a daze. Anyone kind enough to write out the codes, please? Eternally grateful.

 

[luanders]

Thanks - this works great! I am already seeing tons of potential for this tool. Quick question, is there a way to make it so one login/pw combination works for multiple hidden sheets? for example - if the user needs to see 3 different departments.

Thanks so much.

 

[lenze]

Thanks - this works great! I am already seeing tons of potential for this tool. Quick question, is there a way to make it so one login/pw combination works for multiple hidden sheets? for example - if the user needs to see 3 different departments.

Thanks so much.

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

 

[ndendrinos]

this is for: lyjoyce
follow this link ... I've posted a macro that might help you.
http://www.mrexcel.com/forum/showthread.php?t=469032