|
UserName Property
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. |
UserName Property
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) "AccessHelp" wrote in message ... 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. |
UserName Property
On Dec 5, 10:25 am, AccessHelp
wrote: 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. |
UserName Property
Chip,
Thanks for the code. What is the Options dialog? "Chip Pearson" wrote: 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) "AccessHelp" wrote in message ... 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. |
UserName Property
In Excel, ToolsOptionsGeneral, there is a name input box there.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "AccessHelp" wrote in message ... Chip, Thanks for the code. What is the Options dialog? "Chip Pearson" wrote: 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) "AccessHelp" wrote in message ... 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. |
UserName Property
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 -- JB "AccessHelp" wrote: 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. |
UserName Property
I want to thank all of you for your helps. You guys have given me various
options for my needs. Thanks again very much. "john" wrote: 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 -- JB "AccessHelp" wrote: 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. |
UserName Property
Bob,
How did a name get into that input box (ToolsOptionsGeneral)? Is it populated by the system? Is it best to use "Application.UserName" or "Environ("UserName")" to check an authorized user? Thanks. "Bob Phillips" wrote: In Excel, ToolsOptionsGeneral, there is a name input box there. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "AccessHelp" wrote in message ... Chip, Thanks for the code. What is the Options dialog? "Chip Pearson" wrote: 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) "AccessHelp" wrote in message ... 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. |
UserName Property
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" wrote in message ... Bob, How did a name get into that input box (ToolsOptionsGeneral)? Is it populated by the system? Is it best to use "Application.UserName" or "Environ("UserName")" to check an authorized user? Thanks. "Bob Phillips" wrote: In Excel, ToolsOptionsGeneral, there is a name input box there. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "AccessHelp" wrote in message ... Chip, Thanks for the code. What is the Options dialog? "Chip Pearson" wrote: 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) "AccessHelp" wrote in message ... 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. |
UserName Property
Chip,
Thanks for taking the time to explain to me. You are right about the "Environ("UserName"). "Chip Pearson" wrote: 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" wrote in message ... Bob, How did a name get into that input box (ToolsOptionsGeneral)? Is it populated by the system? Is it best to use "Application.UserName" or "Environ("UserName")" to check an authorized user? Thanks. "Bob Phillips" wrote: In Excel, ToolsOptionsGeneral, there is a name input box there. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "AccessHelp" wrote in message ... Chip, Thanks for the code. What is the Options dialog? "Chip Pearson" wrote: 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) "AccessHelp" wrote in message ... 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. |
UserName Property
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 "Chip Pearson" wrote: 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) "AccessHelp" wrote in message ... 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. |
UserName Property
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). -- Rick (MVP - Excel) "Accesshelp" wrote in message ... 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 "Chip Pearson" wrote: 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) "AccessHelp" wrote in message ... 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. |
UserName Property
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. "Rick Rothstein" wrote: 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). -- Rick (MVP - Excel) "Accesshelp" wrote in message ... 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 "Chip Pearson" wrote: 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) "AccessHelp" wrote in message ... 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. . |
| All times are GMT +1. The time now is 06:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com