Test on Username

[Jen]

Hi There,

I want to test whether the person who wants to open a file is entitled to it
or not ...
I am testing it with the Username in Excel versus an array with Usernames.
The problem is that my code says always that it is a "good user"... even if
the username under Tools>Options>General is "dfhdgkj"!

Any insight on where I am missing the ball?

Somewhere on the NG I found the code from Norman Jones, so 99% of the credit
goes to him ... the 1% I changed made it work not properly though
Private Sub Workbook_Open()
Dim s As Integer
Dim vh As Integer

Arr = VBA.Array("Guest", "Jim", "Bob", "Norman")
Res = Application.Match(Environ("UserName"), Arr, 0)
If IsError(Res) Then
MsgBox "GOOD user"

Else
MsgBox "BAD user"
End If
End Sub

Thanks for your help!
Jen

 

[Jen]

Small Update:

I am aware that Environ("UserName") applies to the WWindows Logon and
Application:USerName for the Username in the Excel application ....

Nevertheless Environ("UserName") should return False ...as my Windows Logon
is nor Guest, Jim, Bob or Norman ...

Any thoughts?
Jen

 

[Jen]

Reversing the test could help ... dooh.
Sorrry! Jen

Private Sub Workbook_Open()
Dim s As Integer
Dim vh As Integer

Arr = VBA.Array("Guest", "Jim", "Bob", "Norman")
'Res = Application.Match(Environ("UserName"), Arr, 0)
Res = Application.Match(Application.UserName, Arr, 0)
If IsError(Res) Then

MsgBox "BAD user"
Else
MsgBox "good user"
End If
End Sub

 

[Dave Peterson]

I'm not sure how False fits in (maybe you meant "bad user"??)

But maybe you could debug your code by adding a line and seeing what the VBE
immediate window shows:

Option Explicit
Private Sub Workbook_Open()
Dim res As Variant
Dim Arr As Variant
Arr = Array("Guest", "Jim", "Bob", "Norman")
Debug.Print "***" & Environ("UserName") & "***"
res = Application.Match(Environ("UserName"), Arr, 0)
If IsError(res) Then
MsgBox "GOOD user"
Else
MsgBox "BAD user"
End If
End Sub

If the problem is that you're not getting any message when you open the
workbook, make sure you enable macros for this workbook when/if you're prompted.

And this workbook_open procedure goes behind ThisWorkbook.

 

[Dave Peterson]

Oops. I didn't see your reply.

Reversing the test could help ... dooh.
Sorrry! Jen

Private Sub Workbook_Open()
Dim s As Integer
Dim vh As Integer

Arr = VBA.Array("Guest", "Jim", "Bob", "Norman")
'Res = Application.Match(Environ("UserName"), Arr, 0)
Res = Application.Match(Application.UserName, Arr, 0)
If IsError(Res) Then

MsgBox "BAD user"
Else
MsgBox "good user"
End If
End Sub