Environ function with several users

user_10559764 0
Viewed 241

Im trying to use the environ function to only allow certain users to use a document

Here is my issue: it works with one username, not with several usernames....

I know nested loops could be a solution here but I think there is probably an easier way. Reference tables also didn’t work successfully

The current code looks like 

If ((IDnumber=“12345”) or (IDnumber=“1234”) or IDnumber=“123”))  then 
Msgbox “approved”
Else: msgbox “denied”
vba excel
3 Answers

Select Case is the appropriate statement to use, here:

Dim strMsg as String
Select Case IDnumber
    Case 12345, 1234, 123
        strMsg = "Approved"
    Case Else
        strMsg = "Denied"
End Select
Msgbox strMsg

If your variable IDnumber is a string (in which case, bad choice of variable name!) then use quotes around each item in the list:

Dim strMsg as String
Select Case IDnumber
    Case "alpha", "bravo", "charlie"
        strMsg = "Approved"
    Case Else
        strMsg = "Denied"
End Select
Msgbox strMsg
user_84665230

One could argue if using Select Case (as Olly suggests) or If is the better solution. However, the syntax for the If would be

If IDnumber = "12345" Or IDnumber = "1234" Or IDnumber = "123" Then
    MsgBox "approved"
Else
    MsgBox "denied"
End if

You have several mistakes in your code snippet:

  • unbalanced parentheses. In your case, you don't need any parentheses at all. In VBA, (unlike in many other languages), it is not needed to wrap the whole logical term in parentheses. Also, no need to wrap the single or-conditions into parentheses.
  • As Marcucciboy2 said in a comment, wrong quote characters, use "
  • The syntax else: MsgBox is valid but not a good style. The : separates two statements, it's better to write this in 2 lines. Anyhow, you need an ending End If statement
user_75997980

The allowed usernames could be passed as an Array, and then check whether the current username is in that array. The valueInArray() does exactly this:

Sub CheckUser()

    Dim userNames As Variant
    userNames = Array("User1", "User2", "User3")

    If valueInArray(Environ("UserName"), userNames) Then
        Debug.Print "User Present"
    Else
        Debug.Print "User Not Present"
    End If

End Sub


Public Function valueInArray(myValue As Variant, myArray As Variant) As Boolean

    Dim cnt As Long
    For cnt = LBound(myArray) To UBound(myArray)
        If LCase(CStr(myValue)) = CStr(myArray(cnt)) Then
            valueInArray = True
            Exit Function
        End If
    Next cnt

End Function
user_54486260
Related