Allow Individual access to and Excel sheet 1

[gizmo1973]

I have an Excel 2000 workbook which has 7 sheets named for each employee.
We now want to allow access to each sheet on an individual basis, I thought of full sheet protection then issuing the password to each indivdual but I still really need the protection on the sheet.
I was thinking of splitting the Book into individual Books and then putting thme in folders and locking the NT password to the individual user name but then it just means much more work.
In short is there a code that will not allow anywork on the sheet until a password has been entered other than the protect sheet function.

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[xlbo]

Phil - utilise the sheet ACTIVATE event. Prompt for password based on username and if correct, allow sheet activation - else activate the menu sheet or some other non sensitive sheet...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[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

The workbook will open with only the Intro sheet showing, even if they disable macros. Have a button on the intro sheet that says get My data or something and link it to the NoPeekingNow sub. When they hit the button they will be prompted for their password, and on receipt of the correct password will get just their data. Make sure you protect the code as well though, else they will see it. Nothing's totally secure though, so be careful what you put in. Will
withstand most users casual access attempts though.

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[xlbo]

OMG - Ken does code shocker !!

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DPlank]

Phil,

Suggest you should VBA the sheets visible property to xlVeryHidden
You could then use the Environ("USERNAME") to choose which sheet to make visible. Something like:

Private Sub Workbook_Open()
For Each ws In Worksheets
ws.Visible = xlVeryHidden
Next ws
uName = Environ("USERNAME")
Worksheets(uName).Visible = True
End Sub

and use this in the BeforeClose event

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each ws In Worksheets
ws.Visible = xlVeryHidden
Next ws
End Sub

Mind you this assumes that the sheets are named with the user's network ID. If this isn't the case, substitue a series of IF statements to open up the correct sheet for the user.

Hope this Helps

Dave

"Probably the only Test Analyst on Tek-Tips"

Take a look at Forum1393 & sign up if you'd like

 

[xlbo]

or utilise a lookup table on a "lookups" sheet

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

OMG - Ken does code shocker !!

rotflmao - Hey every now and then you know

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[gizmo1973]

Cheers all got it working eventually!!!!

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[KenWright]

Good stuff, but I'm just going to hammer home the point once more - There is no secure way of doing this in Excel, so if any of this data is confidential and pertains to the employee then you need to think real hard about using Excel for this.

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[gizmo1973]

It's only so they don't cheat and finish each others work off. Now confidential/financial etc involved

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[KenWright]

LOL - If someone was willing to finish my work off I'd give them my password in a heartbeat, and then go home for a rest!!!!!

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[gizmo1973]

That's my thoughts exactly!
I'm still considering a folder per person locked to that user and me only!
Then if the give there NT logon out it's P45 time!!!! (the sack)

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts