show different information based on a password for a workbook

[avgrin]

How to show to sales people only individual reports based on a password to a workbook. The workbook contains a sheet with sales data for the company; by selecting a sales rep in a pivot table filter, it produces necessary reports. How to show to the sales manager all the reports, but to each rep only individual reports?

Thank you,
Avgrin

 

[Luka_LB]

Hi

Sub Z9_Start()'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next

Dim MyPassword As String
MyPassword = "QWERTY"

Sheets("check").Visible = True
ActiveSheet.Unprotect (MyPassword)





Sheets("check").Select
ActiveSheet.Unprotect (MyPassword)

Dim strName As String

'InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
strName = InputBox("Enter Password", "Department of Statistics")
'Exit sub if Cancel button used or no text entered
If strName = vbNullString Then Exit Sub


Sheets("check").Range("A1").Select
ActiveCell = strName

If Sheets("check").Range("A1").Value = "RED" Then

Sheets("RED").Visible = True
Sheets("RED").Select
ActiveSheet.Unprotect (MyPassword)

Else
If Sheets("check").Range("A1").Value = "BLUE" Then

Sheets("BLUE").Visible = True
Sheets("BLUE").Select
ActiveSheet.Unprotect (MyPassword)

Else
If Sheets("check").Range("A1").Value = "GREEN" Then

Sheets("GREEN").Visible = True
Sheets("GREEN").Select
ActiveSheet.Unprotect (MyPassword)



Else
MsgBox "Unable to access data" & vbNewLine & "Incorrect Password", , "Department of Statistics"
Sheets("start").Select

End If
End If
End If
Sheets("check").Visible = False


End Sub

I think this code will help you with that

so, predefined passwords are
"BLUE" -> will open sheet named BLUE
"RED" -> will open sheet named RED
"GREEN" -> will open sheet named GREEN

if smth else is entered error message is displayed

and also you have to protect workbook to disable unhide/hide sheets manualy