Show / Hide specific sheets per username

[byte]

Hi all,

I'm trying to only show specific sheets per user using the environ variable and this code seems to work for the single user / sheet but the master user does not function correctly i.e. the code does not show all sheets, this is the code I am using:

Option Explicit

Private Sub Workbook_Open()
    Dim UserList As Variant
    Dim SheetList As Variant
    Dim i As Integer, ShowSheet As Integer, j As Integer
    Dim MasterUser As String, CurrentUser As String
    
    UserList = Array("John", "Joe")
    SheetList = Array("US", "Combined")
    
    'this user can see all ** the sheets
    MasterUser = "Jane"
    
    'see who the user is
    CurrentUser = Environ("username")
    
    'set ShowSheet to -1 so no sheets are shown if the user isn't found
    ShowSheet = -1
    
    'find the index ** the correct sheet for this user
    For i = LBound(UserList) To UBound(UserList)
        If UCase(UserList(i)) = UCase(CurrentUser) Then
            ShowSheet = i
            Exit For
        End If
    Next i
    
    'hide all sheets except the one the user should see
    With ThisWorkbook
        For j = LBound(SheetList) To UBound(SheetList)
            If j = ShowSheet Or CurrentUser = UCase(MasterUser) Then
                .Sheets(SheetList(j)).Visible = xlSheetVisible
            Else
                .Sheets(SheetList(j)).Visible = xlSheetVeryHidden
            End If
        Next j
    End With
End Sub

Does anyone know why the above code does not respect the Master User "Jane" should be able to see all sheets?

---

Original source for this code was found here:

[Kaper]

I have not checked the rest ** the code, but if it works with other users, just add the blue parts to show all sheets to Masteruser

Private Sub Workbook_Open()
    Dim UserList As Variant
    Dim SheetList As Variant
    Dim i As Integer, ShowSheet As Integer, j As Integer
    Dim MasterUser As String, CurrentUser As String
    
    UserList = Array("John", "Joe")
    SheetList = Array("US", "Combined")
    
    'this user can see all ** the sheets
    MasterUser = "Jane"
    
    'see who the user is
    CurrentUser = Environ("username")
    
    if currentuser = masteruser then
      for i = 1 to sheets.count
        sheets(i).visible = xlSheetVisible
      next i
   else

    'set ShowSheet to -1 so no sheets are shown if the user isn't found
    ShowSheet = -1
    
    'find the index ** the correct sheet for this user
    For i = LBound(UserList) To UBound(UserList)
        If UCase(UserList(i)) = UCase(CurrentUser) Then
            ShowSheet = i
            Exit For
        End If
    Next i
    
    'hide all sheets except the one the user should see
    With ThisWorkbook
        For j = LBound(SheetList) To UBound(SheetList)
            If j = ShowSheet Or CurrentUser = UCase(MasterUser) Then
                .Sheets(SheetList(j)).Visible = xlSheetVisible
            Else
                .Sheets(SheetList(j)).Visible = xlSheetVeryHidden
            End If
        Next j
    End With
  end if
End Sub

[byte]

Hi Kaper,

Thanks but this doesn't affect anything i.e. doesn't respect the master user chosen.

[Kaper]

First question is about Jane ;-)
Have you tried:

sub test()
msgbox ">" &  Environ("username") & "<"
end sub

Does it show exactly

if not - correct the code.
May be Ucase or Trim is needed.

[byte]

Hi Kaper,

Thanks again for your reply. Yes, I have double, triple checked the username and it does show Jane, I have also tried with UCase...

    If UCase(CurrentUser) = UCase(MasterUser) Then
      For i = 1 To Sheets.Count
        Sheets(i).Visible = xlSheetVisible
      Next i
   Else

But the code still does not show all sheets to master user

[Kaper]

Sorry to say so - the problem is not just with the code - it is probably elsewhere.
You have shown no sample workbook so hard to judge.
Try the attached and compare yourself the differences.

[byte]

Thanks Kaper, not sure how or why but I copied the code from your sample (sorry about the no sample on my side) and this worked in my workbook...again I am not so sure why but maybe I had a slight code error in my original :~

Thanks again Kaper

[Kaper]

I am glad to hear that. It is good time to mark thread as solved.
See http://www.excelforum.com/forum-rule...rum-rules.html how to do it.