If current user of Excel workbook is not in Array then hide columns ("O:P")

Question

I have a Array of users of a workbook that I want if the username is not in my Array then columns (O:P) must be hidden.

I got a Type Mismatch error and have no idea what the problem is. Thanks

Here is my code:

userlist = Array("user1", "user2", "user3", "user4")
If Application.UserName <> userlist Then
Worksheets("Master").Range("O:P").EntireColumn.Hidden = True
End If

Answer 1

The problem is the code tries to test whether a string is equal to an array of strings. Strings and arrays are different data types, and that produces the error.

If you want to test whether a string is equal to any of the strings in an array, this is a common method:

userlist = Array("user1", "user2", "user3", "user4")
  If UBound(Filter(userlist, Application.UserName)) > -1 Then
  Worksheets("Master").Range("O:P").EntireColumn.Hidden = True
  End If

Filter will filter the array to items that match UserName.

If the filtered array has one matching item, UBound will equal 0. If the filtered array has no matching items, UBound will equal -1.

Answer 2

There are multiple approaches to check the existence of a username.

1. for loop

userlist = Array("user1", "user2", "user3", "user4")
bFound = False
For i = LBound(userlist) To UBound(userlist)
   If userlist(i) = Application.UserName Then
      bFound = True
      Exit For
   End If
Next
If Not bFound Then _
  Worksheets("Master").Range("O:P").EntireColumn.Hidden = True

2. instr

userlist = Array("user1", "user2", "user3", "user4")
strUserlist = "|" & Join(userlist, "|") & "|"
strUser = "|" & Application.UserName & "|"
If InStr(1, strUserlist, strUser) = 0 Then _
  Worksheets("Master").Range("O:P").EntireColumn.Hidden = True

3. Filter

userlist = Array("user1", "user2", "user3", "user4")
strUser = Application.UserName
If UBound(Filter(userlist, strUser)) = -1 Then _
  Worksheets("Master").Range("O:P").EntireColumn.Hidden = True

Answer 3

Is User Name Found in Array (Application.Match)?

Dim UserList(): UserList = Array("user1", "user2", "user3", "user4")

If IsError(Application.Match(Application.UserName, UserList, 0)) Then
    Worksheets("Master").Columns("O:P").Hidden = True
End If