How to refer multiple sheets in case statements

Question

Goal and Problem

My goal is to restrict access to different worksheets according to the username that currently uses the excel file.

I will have a minimum of 14 users (1 admin and 13 heads of department) and each one will have different access do the multiple existents worksheets. The admin will have access to all worksheets while the heads of department will each have access to a worksheet only associated with their department and at least 2 or 3 other worksheets.

Currently, I'm able to grant access to one worksheet but, as I said previously, I want them to access multiple worksheets.

What I've tried

I've tried to use arrays in multiple ways but none of them worked so far.

    Select Case Application.UserName        
        Case "User 2"
            Set GetAllowedSheet = Sheets(Array("Sheet2", "Sheet3", "Sheet4"))

Dim ArrayOne as Variant
ArrayOne = Array("Sheet2", "Sheet3", "Sheet4")

    Select Case Application.UserName        
        Case "User 2"
            Set GetAllowedSheet = Sheets(ArrayOne)

I did some research on google but nothing seems to quite match what I'm looking for.

Code

Private Sub Workbook_Open()
    Showorksheets
End Sub

Sub Showorksheets()

    Dim ws As Worksheet
    Dim wsAllowed As Worksheet

    If Application.UserName = "User 0" Then
        For Each ws In Worksheets
            ws.Visible = xlSheetVisible
        Next
        Exit Sub
    End If

    Set wsAllowed = GetAllowedSheet
    wsAllowed.Visible = xlSheetVisible

    For Each ws In Worksheets
        If ws.Name <> wsAllowed.Name Then
            ws.Visible = xlSheetHidden
        End If
    Next

End Sub

Function GetAllowedSheet() As Worksheet

    Select Case Application.UserName
        Case "User 1"
            Set GetAllowedSheet = Sheets("Sheet1")
        Case "User 2"
            Set GetAllowedSheet = Sheets("Sheet2")
        Case "User 3"
            Set GetAllowedSheet = Sheets("Sheet3")
        '...
        Case Else
        '...

    End Select

End Function

Answer 1

As @BigBen suggest, hiding/unhiding is not the best way, because it can be easily bypassed.

Also, I do not know if there are any other macros in that workbook that affect worksheets, but dealing with hidden worksheets while coding can be a headache.

But anyways something like this could help.

Private Sub Workbook_Open()
'A workbook must have always at least 1 visible worksheet
Application.ScreenUpdating = False


Dim DictWK As Object
Dim UserLevel As Byte
Dim wk As Worksheet

Set DictWK = CreateObject("Scripting.Dictionary")

With ThisWorkbook
    DictWK.Add .Worksheets("ONLY ADMIN").Name, 0 '0 because only admin can have it
    DictWK.Add .Worksheets("ADMIN AND HEADERS").Name, 1
    DictWK.Add .Worksheets("ASSISTANTS").Name, 2
    DictWK.Add .Worksheets("EVERYBODY").Name, 99 'A workbook must have at least 1 visible worksheet, so make sure there is 1 always visible to everybody
End With

UserLevel = LVL_ACCESS("User 1") 'change this to however you detect the username

For Each wk In ThisWorkbook.Worksheets
    If UserLevel <= DictWK(wk.Name) Then
        wk.Visible = xlSheetVisible
    Else
        wk.Visible = xlSheetHidden
    End If
Next wk

DictWK.RemoveAll
Set DictWK = Nothing
Application.ScreenUpdating = True
End Sub

User's level:

Function LVL_ACCESS(ByVal vUsername As String) As Byte
Select Case vUsername
    Case "User 1"
        LVL_ACCESS = 0
    Case "User 2"
        LVL_ACCESS = 1
    Case "User 3"
        LVL_ACCESS = 2
    Case Else
        'not recognized, no access
        LVL_ACCESS = 99
End Select
End Function

Uploaded a sample to Gdrive: https://drive.google.com/open?id=1mI3LQd8QxLDlMl1bzz5hCFIwdOFCS2Nc