UserName Property

[AccessHelp]

Good morning,

I have a command button in a workbook to run a macro. However, when a user
clicks on the button, the macro will check first automatically whether the
user who clicks on it has an authority to run the macro. If not, the user
will receive a message "Access denied.".

So I came up with the following code:

If Application.UserName <> "John Doe" Then
Msgbox "Access denied."
Else
run the remainder of code......
End If

2 questions:

1. What does the UserName Property check against with? Does it check
against with Windows user name or computer user name? When I did the code
"Msgbox Application.UserName", I got my first and last names.
2. I will have more than one authorized users, and instead of having the
code "If Application.UserName <> "John Doe" Or If Application.UserName <>
"John Smith" Or ......", can you help me with better coding?

Thanks.

 

[Chip Pearson]

Application.UserName is completely independent of the Windows logon name.
The UserName is simply what is entered in the Options dialog. You can get
the actual Windows user name with

Dim UName As String
UName = Environ("Username")

For multiple authorized users, use code like

Dim UName As String
UName = LCase(Environ("UserName")) ' for to all lower case
Select Case UName
Case "name one", "name two", "name three" ' use lower case names
' authorized users
Case Else
' unauthorized user
End Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Alok]

Good morning,

I have a command button in a workbook to run a macro. However, when a user
clicks on the button, the macro will check first automatically whether the
user who clicks on it has an authority to run the macro. If not, the user
will receive a message "Access denied.".

So I came up with the following code:

If Application.UserName <> "John Doe" Then
Msgbox "Access denied."
Else
run the remainder of code......
End If

2 questions:

1. What does the UserName Property check against with? Does it check
against with Windows user name or computer user name? When I did the code
"Msgbox Application.UserName", I got my first and last names.
2. I will have more than one authorized users, and instead of having the
code "If Application.UserName <> "John Doe" Or If Application.UserName <>
"John Smith" Or ......", can you help me with better coding?

Thanks.

Not sure about the first question, but for the second I would make a
"IsAuthorizedUser(username As String) As Boolean" function, and call
it like

If Not IsAuthorizedUser(Application.UserName) Then
Msgbox "Access denied."
Else
run the remainder of code......
End If

If there aren't too many authorized users, I would think you could
structure IsAuthorizedUser as follows:

Function IsAuthorizedUser(username As String) As Boolean
Select Case username
Case "Joe Smith"
IsAuthorizedUser = True
Case "Jane Doe"
IsAuthorizedUser = True
Case Else
IsAuthorizedUser = False
End Select
End Function

But whatever you think is readable would work.

 

[AccessHelp]

Chip,

Thanks for the code. What is the Options dialog?

 

[Bob Phillips]

In Excel, Tools>Options>General, there is a name input box there.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[john]

Rather than "Hard Code" the user names you could add them to a worksheet &
use code to check against this for valid names:

something like following may work for you - Just add all user names in Col A
in Sheet1 from row 1 onwards - you can rename sheet but remember to change
code:

Sub CheckUserName()
Dim validuser As Boolean
Set rng = Worksheets("Sheet1").Range("A1").CurrentRegion
numrows = rng.Rows.Count
validuser = False
For i = 1 To numrows
If rng(i).Value = Application.UserName Then
validuser = True
Exit For
End If
Next

If validuser = True Then
MsgBox "Valid User"
'code here

Else

MsgBox "Invalid User"

'code her

End If
End Sub

Hope helpful

 

[AccessHelp]

I want to thank all of you for your helps. You guys have given me various
options for my needs.

Thanks again very much.

 

[AccessHelp]

Bob,

How did a name get into that input box (Tools>Options>General)? Is it
populated by the system? Is it best to use "Application.UserName" or
"Environ("UserName")" to check an authorized user?

Thanks.

 

[Chip Pearson]

The value of the User Name is initialized to the name supplied when Office
was installed. If Office was installed by the manufacturer and came with the
PC, it is often set to something like "Satisfied Dell Customer" (a bit
presumptuous on their part, I think). It can be changed any time.

For your authorized users, you should absolutely use the Windows logon name,
not Excel's UserName property. If an unauthorized happens to know who is an
authorized user, he could simply change the Application's UserName property
to a valid value. You can't change the value returned by
Environ("UserName"), except, of course, by logging on as another user.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[AccessHelp]

Chip,

Thanks for taking the time to explain to me. You are right about the
"Environ("UserName").

 

[Accesshelp]

Chip,

Two years ago you helped me with the code below for username, and I am still
using it.

I have about 30 users on the code where you see Case "name one", "name
two",.... Somehow, I am having problems with two of the usernames. Whenever
these two users use the file, the file keeps saying unauthorized users. I
asked them many times to make sure that the usernames that I have are
correct. In addition, I asked them to go into My Computer Properties and
asked them to see the username under Environment Variables. I also confirmed
their usernames with my IT department. They all correct.

All 30 users are located in various offices/states, and these are the only
two users having problems.

At this point, I don't know what causes the problems on the two users.

Can you think of anything that would cause these two usernames problems?

Thanks.

Dim UName As String
UName = LCase(Environ("UserName")) ' for to all lower case
Select Case UName
Case "name one", "name two", "name three" ' use lower case names
' authorized users
Case Else
' unauthorized user
End Select

 

[Rick Rothstein]

It might help if you told us the names are? Do they use any accented
letters? You could put a break point on the first Case statement and then
look at the contents of the UName variable to see what is actually in it.
Also test to see if the user has accidentally tagged on any spaces to the
name (also make sure you didn't do this on your "test against" names
either).

 

[Accesshelp]

Rick,

Thanks for your response.

What are accented letters?

Basically, the usernames are the users' first initial and last name (no
spaces in between first initial and last name). For example, the username
that I have for John Dole in my Case statement is jdole. As far as any
spaces to the names, I don't think I would have that issue because I check
their names in their email properties and check with our IT department.

As far as when you said "You could put a break point on the first Case
statement...", I am not sure what you mean.

The structure of my Case statement is similar to the following:

dim UName as String
UName = LCase(environ("UserName"))
Select Case UName
Case "jdole", "adole", "bdole", ... & _
"cdole", "ddole",..... & _
"edole","fdole"
'perform the following code
Else Case
'perform the following code
End Select

Thanks.