User ID based excel macro for performance data

[Rajchettiar]

Hi ,
I manage performance data of a large team. How can share full data file to the entire team in such a way where
1. If a team members opens the file he can only view his performance data.
2. A Team leader can view only his teams data.
3. Unit head/ Admin can view the entire data.
In my organization every individual has a unique log in ID.

 

[Zot]

I'm giving you one possible solution. Except for Main sheet, the rest of sheets in workbook will be hidden and cannot be unhide unless through VBA.
Once password is entered. the person will have access it whichever sheet the person is allowed.

One routine is event routine when workbook is opened, installed in ThisWorkbook module. the other one is in regular module.

Modify the code to your need.

Private Sub Workbook_Open()
Dim ws As Worksheet

For Each ws In Sheets
    If Not ws.Name = "Main" Then
        If ws.Visible = xlSheetVisible Then
            ws.Visible = xlSheetVeryHidden
        End If
    End If
Next

Call Test

End Sub

Sub Test()

Const AdminPwd = "1234"
Const TeamLeaderPwd = "123"
Const MemID01Pwd = "12"
Dim Pwd As String
Dim ws As Worksheet
Dim wsMain As Worksheet
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Set wsMain = Sheets("Main")
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")

Pwd = InputBox("Enter Password")
Select Case Pwd
    Case AdminPwd
        For Each ws In Sheets
            ws.Visible = xlSheetVisible
        Next
    Case TeamLeaderPwd
        ws1.Visible = xlSheetVisible
        ws3.Visible = xlSheetVisible
    Case MemID01Pwd
        ws1.Visible = xlSheetVisible
End Select

End Sub