VBA Code Help!!

[Maebus]

I am creating a workbook to display employee performance stats with a separate sheet for each supervisor. I want it to work that each supervisor can only view their individual sheet and all others are very hidden so they cannot view other supervisors employees. I have code but when I have people test they are still able to view and select all worksheets. What am I missing or doing wrong? Here is what I have. Thanks in advance for any help.

Private Sub Workbook_Open()
Dim currentUser As String

' Get the current user
currentUser = Environ("USERNAME")

' Allow access to specific worksheets for specific users
Select Case currentUser
Case "JR", "A"
' Allow access to the worksheet named "A" for specific users
Sheets("A").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("B").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("D").Visible = xlSheetVeryHidden
Sheets("I").Visible = xlSheetVeryHidden
Sheets("R").Visible = xlSheetVeryHidden
Sheets("S").Visible = xlSheetVeryHidden
Sheets("M").Visible = xlSheetVeryHidden
Case "B", "JR"
' Allow access to the worksheet named "B" for a specific user
Sheets("B").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("A").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("D").Visible = xlSheetVeryHidden
Sheets("I").Visible = xlSheetVeryHidden
Sheets("R").Visible = xlSheetVeryHidden
Sheets("S").Visible = xlSheetVeryHidden
Sheets("M").Visible = xlSheetVeryHidden
Case "D", "JR"
' Allow access to the worksheet named "D" for a specific user
Sheets("D").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("A").Visible = xlSheetVeryHidden
Sheets("B").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("I").Visible = xlSheetVeryHidden
Sheets("R").Visible = xlSheetVeryHidden
Sheets("S").Visible = xlSheetVeryHidden
Sheets("M").Visible = xlSheetVeryHidden
Case "I", "JR"
' Allow access to the worksheet named "I" for a specific user
Sheets("I").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("A").Visible = xlSheetVeryHidden
Sheets("B").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("D").Visible = xlSheetVeryHidden
Sheets("R").Visible = xlSheetVeryHidden
Sheets("S").Visible = xlSheetVeryHidden
Sheets("M").Visible = xlSheetVeryHidden
Case "R", "JR"
' Allow access to the worksheet named "R" for a specific user
Sheets("R").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("A").Visible = xlSheetVeryHidden
Sheets("B").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("D").Visible = xlSheetVeryHidden
Sheets("I").Visible = xlSheetVeryHidden
Sheets("S").Visible = xlSheetVeryHidden
Sheets("M").Visible = xlSheetVeryHidden
Case "S", "JR"
' Allow access to the worksheet named "S" for a specific user
Sheets("S").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("A").Visible = xlSheetVeryHidden
Sheets("B").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("D").Visible = xlSheetVeryHidden
Sheets("I").Visible = xlSheetVeryHidden
Sheets("R").Visible = xlSheetVeryHidden
Sheets("M").Visible = xlSheetVeryHidden
Case "M", "JR"
' Allow access to the worksheet named "M" for a specific user
Sheets("M").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("A").Visible = xlSheetVeryHidden
Sheets("B").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("D").Visible = xlSheetVeryHidden
Sheets("I").Visible = xlSheetVeryHidden
Sheets("R").Visible = xlSheetVeryHidden
Sheets("S").Visible = xlSheetVeryHidden
End Select
End Sub

 

[Maebus]

Did you close out the workbook first?

Change to Case for multiple users.

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

Private Sub Workbook_Open()
    Dim currentUser As String
    Dim allowedSheet As String
    Dim ws As Worksheet
    Dim sheetFound As Boolean

    ' Get the current user
    currentUser = Environ("USERNAME")
  
    Select Case currentUser
        Case "JR", "SuperUser1", "SuperUser2"  'Add more super usernames as needed
            Exit Sub
        Case Else
            allowedSheet = currentUser
    End Select

    sheetFound = False
  
    ' Loop through all sheets and set visibility
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = allowedSheet Or ws.Name = "HomePage" Then
            ws.Visible = xlSheetVisible
            sheetFound = True
        Else
            ws.Visible = xlSheetVeryHidden
        End If
    Next ws

    ' If the allowedSheet is not found, show the HomePage sheet
    If Not sheetFound Then
        Worksheets("HomePage").Visible = xlSheetVisible
        MsgBox "User Not Found!" 'comment out if you don't want to show message box
    End If

End Sub

Ok this working as far as no errors but now it’s hiding all but the HomePage

 

[Cubist]

That only happens if there's no name found. Add the debug line and see if your username matches one of the sheet names. The result will be in the Immediate window.

....
    ' Get the current user
    currentUser = Environ("USERNAME")
    Debug.Print "The current user is: " & currentUser
....

 

[Maebus]

That only happens if there's no name found. Add the debug line and see if your username matches one of the sheet names. The result will be in the Immediate window.

....
    ' Get the current user
    currentUser = Environ("USERNAME")
    Debug.Print "The current user is: " & currentUser
....

Thank you master coder. For some reason when I started my username was all lower case but now it’s all uppercase. Very strange.