Password required to see each worksheet

[Rams06]

Excel Question:

I have multiple salespersons data in one workbook....

How can I create multiple users passwords so when they open the workbook they have to enter their salepersons code and password and ONLY their worksheet "pops" up?

This is the ideal Scenario:

I e-mail all the salespeople the same workbook but when they click to open the Excel workbook it asks them for their salesman code and password. After they enter their username and password.... it only shows them their sheet and hides the rest.

Thanks alot for your help.

 

[SkipVought]

Hi,

You will have to use some VBA code.

The workbook should be configured with an Introduction Sheet that EVERYONE sees. All other sheets are VERY HIDDEN. VERY HIDDEN is a state that NO ONE can change unless thru code or in the VBA Editor.

When to workbook opens a FORM asks them for their ID & PW. The code evaluates the response and UNHIDES the appropriate sheet.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[Rams06]

Can you give me a starting point on creating such a thing?

 

[KenWright]

I think you're looking for something along the following lines, just change
sheet names, passwords etc:-

With this in the ThisWorkbook Module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wks As Worksheet
ActiveWorkbook.Unprotect ("abcd")

 For Each wks In ActiveWorkbook.Worksheets
  If wks.Name <> "Intro" Then
    wks.Visible = xlVeryHidden
  End If
 Next wks

With ActiveWorkbook
   .Protect Password:="abcd123efg"
   .Save
End With

End Sub

-----------------------------------------------------------


and with the following in a normal module

Sub NoPeekingNow()

Dim Ans As String

ActiveWorkbook.Unprotect ("abcd123efg")
Ans = InputBox("Please input your password")

If Ans = "cdef" Then
     Sheets("Sheet1").Visible = True
     Sheets("Sheet2").Visible = True
   Else
    If Ans = "ghij" Then
      Sheets("Sheet3").Visible = True
      Sheets("Sheet4").Visible = True
    Else
     If Ans = "hijk" Then
       Sheets("Sheet5").Visible = True
       Sheets("Sheet6").Visible = True
      Else
       If Ans = "ijkl" Then
         Sheets("Sheet7").Visible = True
         Sheets("Sheet8").Visible = True
       Else
         If Ans = "klmn" Then
           Sheets("Sheet9").Visible = True
           Sheets("Sheet10").Visible = True
          Else
            If Ans = "lmnp" Then
              Sheets("Sheet11").Visible = True
              Sheets("Sheet12").Visible = True

              Else: MsgBox "Try Again, or Stop if you're not supposed to be
trying"
     End If
    End If
   End If
  End If
 End If
End If

ActiveWorkbook.Protect Password:="abcd"

End Sub

Bear in mind though, that nothing is absolutely secure.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

Have you ever created a Userform?

alt+F11 toggles between the VB Editor and ActiveSheet.

In the VB Editor, Insert/Userform adds a userform.

You will need to place textboxes, labels and a command button objects on the form.

View the Properties of each selected object to change captions. The Textbox object has a PasswordChar (this is where the asterisks or whatever come from when you enter a password)

The CommandButton_Click code (double click the button) will process the user's entries in the texboxes with code something like this...

Private Sub CommandButton1_Click()
   Select Case TextBox1.Text
      Case "ID1"
         If TextBox2.Text <> "qwerty" Then Exit Sub
      Case "ID2"
         If TextBox2.Text <> "asdfgh" Then Exit Sub
   End Select
   With Sheets(TextBox1.Text)
      .Visible = xlSheetVisible
      .Activate
   End With
End Sub

Where each sheet name is the same as the user ID. I have the passwords hard coded, but they could be in a liet on another VERY HIDDEN sheet that only you have access to. ALSO, the VB Project needs to be password protected. In the VB Editor - Tools/VBA Project Properties.

SAVE THE PASSWORD in a safe place.


Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[AnotherHiggins]

Ken brings up a very good point. If this is just a handy way of filtering the information visible to individuals, then this is a fine idea.

If, on the other hand, there is proprietary information contained in the hidden worksheets, this is not the way to go! If so inclined, any of the recipients can spend 10 minutes on an Internet search engine and easily find ways to defeat this "protection" and unhide another salesperson's information!!

If this would be a big no-no, then you'd probably be better off setting up code to generate separate workbooks and then email each to the appropriate recipient. That way, there is no one else's info in the workbook they receive.

Again, if this data isn't secret, then the way you're going is fine.

John

Oh pointy bird,
Oh pointy pointy,
Anoint my head,
Anointy nointy.
Steve Martin in The Man With 2 Brains and L.A. Story
To get the best answers fast, please read faq181-2886

 

[Rams06]

Thank you all for your fine suggestions. YOU the man!!!!