Different Passwords for different worksheets in one workbook

[exalan]

Hi

I am populating a sales template in one Excel workbook but I want business
unit managers to have access to view their respective worksheet only … I want
to set a restriction, maybe by using password.
So, each manager will be given a password and they need to enter it when
opening the file, then they can only view what is within their own worksheet.

Is it possible in Excel to do so?

Please help....

 

[Luke M]

First, Excel does not offer offer a significant level of protection. The
workbook/worksheet passwords can be cracked via macros in about 2-5 minutes.
Or, the managers could simple write a formula that references a hidden sheet
to find out what is stored there!

If you're going more for the conveniece, you could setup a macro that based
on:
Environ ("UserName")

would then decide which sheet to display. But again, if this information is
sensitive at all, your best bet is to place the data in seperate workbooks.

 

[Eduardo]

Hi,
in the worksheet name for each manager right click, then paste the code as
follow, you will have to change the passwword, you will see three places with
the password, in the example below MANAGER. so then you hide the worksheet.
When opening a popup will show up asking for the password

if this helps please click yes thanks


Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to access DATA sheet")

If strPassword = "" Then
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
ElseIf strPassword <> "MANAGER" Then
MsgBox "Password Incorrect "
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If
Range("a1").Select

On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True

 

[Dave Peterson]

You could try, but you're doomed for failure if anyone is really interested.

Excel's security isn't meant for this kind of thing.

Maybe it's time for a separate workbook for each manager.

 

[exalan]

Hi Eduardo

Thanks for the advice.

I've created 3 worksheets "Alan", "Adrian" & "Andrew" and copy & paste the
macros and added the respective passwords as you've guided. However, I can
only open the 2nd and 3rd worksheets. As for the 1st worksheet ("Alan"), it
can open but the screen is locked (blueish screen).

Appreciate if you can investigate and advise.

Best regards

 

[exalan]

Hi Luke

Thanks for your advice.
Best regards

 

[exalan]

Hi Dave

Thanks for your advice.
Best regards

 

[Eduardo]

Hi,
That macro I gave to you is working for me, I have more than 20 sheets, do
something hide all the sheets and then try to open Alan, do something else
include a menu with a buttom called Alan and add this code which will unhide
the sheet to see what happens. Call the sheet where you have the button Menu,
and run the button from there, macro will see into that sheet first

Sub GSG_Target()
'
' GSG_Target Macro
'

'
Sheets("Menu").Select
Sheets("GSG - Alan").Visible = True
Sheets("GSG - Alan").Select

 

[exalan]

Hi Eduardo

Many thanks for the tips. it works!