Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
Hello all,
I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
Are you typing the usernames in lowercase?
Are you sure that there aren't extra space characters in the name string? I'd bet that there was a difference--maybe a couple of transposed characters??? Please Help wrote: Hello all, I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
Hi Dave,
Thanks for your response. Yes, everything is in lower case. In addition, there were no extra space or characters and transposed of characters. That is what puzzles me. Is there a limit on how many username can be used for "Username"? Thanks. "Dave Peterson" wrote: Are you typing the usernames in lowercase? Are you sure that there aren't extra space characters in the name string? I'd bet that there was a difference--maybe a couple of transposed characters??? Please Help wrote: Hello all, I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
19 names should not be a problem, as this select case statement handles
about 1000 characters in the first evaluation. Find out which usernames are failing and investigate them. Sub Test() Dim s As String Dim x As Long For x = 122 To 25 Step -1 s = "test" & x Select Case s Case "test122", "test121", "test120", "test119", "test118", "test117", "test116", "test115", "test114", "test113", "test112", "test111", "test110", "test109", "test108", "test107", "test106", "test105", "test104", "test103", "test102", "test101", "test100", "test99", "test98", "test97", "test96", "test95", "test94", "test93", "test92", "test91", "test90", "test89", "test88", "test87", "test86", "test85", "test84", "test83", "test82", "test81", "test80", "test79", "test78", "test77", "test76", "test75", "test74", "test73", "test72", "test71", "test70", "test69", "test68", "test67", "test66", "test65", "test64", "test63", "test62", "test61", "test60", "test59", "test58", "test57", "test56", "test55", "test54", "test53", "test52", "test51", "test50", "test49", "test48", "test47", "test46", "test45", "test44", "test43", "test42", "test41", "test40", "test39", "test38", "test37", "test36", "test35", "test34", "test33", "test32", "test31", "test30", "test29", "test28", "test27", "test26", "test25" ' OK Case Else Debug.Print s & " not in list" End Select Next End Sub -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "Please Help" wrote in message ... Hi Dave, Thanks for your response. Yes, everything is in lower case. In addition, there were no extra space or characters and transposed of characters. That is what puzzles me. Is there a limit on how many username can be used for "Username"? Thanks. "Dave Peterson" wrote: Are you typing the usernames in lowercase? Are you sure that there aren't extra space characters in the name string? I'd bet that there was a difference--maybe a couple of transposed characters??? Please Help wrote: Hello all, I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
Just to add to Tim's response...
Can you go to the offenders and have them run a small macro: Option Explicit sub testit() debug.print "****" & environ("username") & "****" end sub (the ****'s are just to help the username stand out more) Then look at the immediate window in the VBE and see if you notice the spelling error. I'm gonna bet that you still have a typing difference. =========== Or your code has an uppercase letter that you're missing. You can avoid that by: Select case lcase(...) case is = lcase("test1"), lcase("test2"), .... or add a line to the top of the module that will make all text comparisons non-case sensitive: Option Compare Text Please Help wrote: Hi Dave, Thanks for your response. Yes, everything is in lower case. In addition, there were no extra space or characters and transposed of characters. That is what puzzles me. Is there a limit on how many username can be used for "Username"? Thanks. "Dave Peterson" wrote: Are you typing the usernames in lowercase? Are you sure that there aren't extra space characters in the name string? I'd bet that there was a difference--maybe a couple of transposed characters??? Please Help wrote: Hello all, I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
Good morning Dave,
Unfortunately, the users are located in another state. I double-checked the usernames in my code and in our addressbook. They are the same. As far as case sensitivity, one of the lines in my code converts the usernames to lower case. By having that line, do you think case is still an issue? Thanks. "Dave Peterson" wrote: Just to add to Tim's response... Can you go to the offenders and have them run a small macro: Option Explicit sub testit() debug.print "****" & environ("username") & "****" end sub (the ****'s are just to help the username stand out more) Then look at the immediate window in the VBE and see if you notice the spelling error. I'm gonna bet that you still have a typing difference. =========== Or your code has an uppercase letter that you're missing. You can avoid that by: Select case lcase(...) case is = lcase("test1"), lcase("test2"), .... or add a line to the top of the module that will make all text comparisons non-case sensitive: Option Compare Text Please Help wrote: Hi Dave, Thanks for your response. Yes, everything is in lower case. In addition, there were no extra space or characters and transposed of characters. That is what puzzles me. Is there a limit on how many username can be used for "Username"? Thanks. "Dave Peterson" wrote: Are you typing the usernames in lowercase? Are you sure that there aren't extra space characters in the name string? I'd bet that there was a difference--maybe a couple of transposed characters??? Please Help wrote: Hello all, I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
Tim,
Thanks for your response. I know which usernames are failing. I just don't know why they are failing. As I responded to Dave, I checked the usernames with those users, with our IT and in our addressbook. They are matched to what I have in my code. That is where I get frustrated. Anymore idea? Thanks. "Tim Zych" wrote: 19 names should not be a problem, as this select case statement handles about 1000 characters in the first evaluation. Find out which usernames are failing and investigate them. Sub Test() Dim s As String Dim x As Long For x = 122 To 25 Step -1 s = "test" & x Select Case s Case "test122", "test121", "test120", "test119", "test118", "test117", "test116", "test115", "test114", "test113", "test112", "test111", "test110", "test109", "test108", "test107", "test106", "test105", "test104", "test103", "test102", "test101", "test100", "test99", "test98", "test97", "test96", "test95", "test94", "test93", "test92", "test91", "test90", "test89", "test88", "test87", "test86", "test85", "test84", "test83", "test82", "test81", "test80", "test79", "test78", "test77", "test76", "test75", "test74", "test73", "test72", "test71", "test70", "test69", "test68", "test67", "test66", "test65", "test64", "test63", "test62", "test61", "test60", "test59", "test58", "test57", "test56", "test55", "test54", "test53", "test52", "test51", "test50", "test49", "test48", "test47", "test46", "test45", "test44", "test43", "test42", "test41", "test40", "test39", "test38", "test37", "test36", "test35", "test34", "test33", "test32", "test31", "test30", "test29", "test28", "test27", "test26", "test25" ' OK Case Else Debug.Print s & " not in list" End Select Next End Sub -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "Please Help" wrote in message ... Hi Dave, Thanks for your response. Yes, everything is in lower case. In addition, there were no extra space or characters and transposed of characters. That is what puzzles me. Is there a limit on how many username can be used for "Username"? Thanks. "Dave Peterson" wrote: Are you typing the usernames in lowercase? Are you sure that there aren't extra space characters in the name string? I'd bet that there was a difference--maybe a couple of transposed characters??? Please Help wrote: Hello all, I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
Please Help
Copy/Paste the information sources to two different comumns on a w/s let us say Column A and F Then place this formula in column C "=A1=F1" (Copy Down) then check all of the "False" Trick I learned a long time ago is to =Trim(Clean("YoutText")) EagleOne Please Help wrote: Good morning Dave, Unfortunately, the users are located in another state. I double-checked the usernames in my code and in our addressbook. They are the same. As far as case sensitivity, one of the lines in my code converts the usernames to lower case. By having that line, do you think case is still an issue? Thanks. "Dave Peterson" wrote: Just to add to Tim's response... Can you go to the offenders and have them run a small macro: Option Explicit sub testit() debug.print "****" & environ("username") & "****" end sub (the ****'s are just to help the username stand out more) Then look at the immediate window in the VBE and see if you notice the spelling error. I'm gonna bet that you still have a typing difference. =========== Or your code has an uppercase letter that you're missing. You can avoid that by: Select case lcase(...) case is = lcase("test1"), lcase("test2"), .... or add a line to the top of the module that will make all text comparisons non-case sensitive: Option Compare Text Please Help wrote: Hi Dave, Thanks for your response. Yes, everything is in lower case. In addition, there were no extra space or characters and transposed of characters. That is what puzzles me. Is there a limit on how many username can be used for "Username"? Thanks. "Dave Peterson" wrote: Are you typing the usernames in lowercase? Are you sure that there aren't extra space characters in the name string? I'd bet that there was a difference--maybe a couple of transposed characters??? Please Help wrote: Hello all, I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
EagleOne,
Thanks for your response. I am not sure what this test will accomplish on my situation. Please explain. Thanks. " wrote: Please Help Copy/Paste the information sources to two different comumns on a w/s let us say Column A and F Then place this formula in column C "=A1=F1" (Copy Down) then check all of the "False" Trick I learned a long time ago is to =Trim(Clean("YoutText")) EagleOne Please Help wrote: Good morning Dave, Unfortunately, the users are located in another state. I double-checked the usernames in my code and in our addressbook. They are the same. As far as case sensitivity, one of the lines in my code converts the usernames to lower case. By having that line, do you think case is still an issue? Thanks. "Dave Peterson" wrote: Just to add to Tim's response... Can you go to the offenders and have them run a small macro: Option Explicit sub testit() debug.print "****" & environ("username") & "****" end sub (the ****'s are just to help the username stand out more) Then look at the immediate window in the VBE and see if you notice the spelling error. I'm gonna bet that you still have a typing difference. =========== Or your code has an uppercase letter that you're missing. You can avoid that by: Select case lcase(...) case is = lcase("test1"), lcase("test2"), .... or add a line to the top of the module that will make all text comparisons non-case sensitive: Option Compare Text Please Help wrote: Hi Dave, Thanks for your response. Yes, everything is in lower case. In addition, there were no extra space or characters and transposed of characters. That is what puzzles me. Is there a limit on how many username can be used for "Username"? Thanks. "Dave Peterson" wrote: Are you typing the usernames in lowercase? Are you sure that there aren't extra space characters in the name string? I'd bet that there was a difference--maybe a couple of transposed characters??? Please Help wrote: Hello all, I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
maybe looking for typos, additional spaces or even hidden characters.
I once had a CRLF buried in a username and it took forever to discover that it was actually our SQL developer's bug. drat. "Please Help" wrote in message ... EagleOne, Thanks for your response. I am not sure what this test will accomplish on my situation. Please explain. Thanks. " wrote: Please Help Copy/Paste the information sources to two different comumns on a w/s let us say Column A and F Then place this formula in column C "=A1=F1" (Copy Down) then check all of the "False" Trick I learned a long time ago is to =Trim(Clean("YoutText")) EagleOne Please Help wrote: Good morning Dave, Unfortunately, the users are located in another state. I double-checked the usernames in my code and in our addressbook. They are the same. As far as case sensitivity, one of the lines in my code converts the usernames to lower case. By having that line, do you think case is still an issue? Thanks. "Dave Peterson" wrote: Just to add to Tim's response... Can you go to the offenders and have them run a small macro: Option Explicit sub testit() debug.print "****" & environ("username") & "****" end sub (the ****'s are just to help the username stand out more) Then look at the immediate window in the VBE and see if you notice the spelling error. I'm gonna bet that you still have a typing difference. =========== Or your code has an uppercase letter that you're missing. You can avoid that by: Select case lcase(...) case is = lcase("test1"), lcase("test2"), .... or add a line to the top of the module that will make all text comparisons non-case sensitive: Option Compare Text Please Help wrote: Hi Dave, Thanks for your response. Yes, everything is in lower case. In addition, there were no extra space or characters and transposed of characters. That is what puzzles me. Is there a limit on how many username can be used for "Username"? Thanks. "Dave Peterson" wrote: Are you typing the usernames in lowercase? Are you sure that there aren't extra space characters in the name string? I'd bet that there was a difference--maybe a couple of transposed characters??? Please Help wrote: Hello all, I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
I didn't notice that lcase() line in your original code.
I'd still guess it was a difference in spelling, though. Please Help wrote: Good morning Dave, Unfortunately, the users are located in another state. I double-checked the usernames in my code and in our addressbook. They are the same. As far as case sensitivity, one of the lines in my code converts the usernames to lower case. By having that line, do you think case is still an issue? Thanks. "Dave Peterson" wrote: Just to add to Tim's response... Can you go to the offenders and have them run a small macro: Option Explicit sub testit() debug.print "****" & environ("username") & "****" end sub (the ****'s are just to help the username stand out more) Then look at the immediate window in the VBE and see if you notice the spelling error. I'm gonna bet that you still have a typing difference. =========== Or your code has an uppercase letter that you're missing. You can avoid that by: Select case lcase(...) case is = lcase("test1"), lcase("test2"), .... or add a line to the top of the module that will make all text comparisons non-case sensitive: Option Compare Text Please Help wrote: Hi Dave, Thanks for your response. Yes, everything is in lower case. In addition, there were no extra space or characters and transposed of characters. That is what puzzles me. Is there a limit on how many username can be used for "Username"? Thanks. "Dave Peterson" wrote: Are you typing the usernames in lowercase? Are you sure that there aren't extra space characters in the name string? I'd bet that there was a difference--maybe a couple of transposed characters??? Please Help wrote: Hello all, I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
ps.
And send those users with problems a workbook to open that populates a cell with their username. Tell them to save the file and return it to you. worksheets("sheet1").range("a1").value = "****" & environ("username") & "****" Maybe you'll see the difference. Dave Peterson wrote: I didn't notice that lcase() line in your original code. I'd still guess it was a difference in spelling, though. Please Help wrote: Good morning Dave, Unfortunately, the users are located in another state. I double-checked the usernames in my code and in our addressbook. They are the same. As far as case sensitivity, one of the lines in my code converts the usernames to lower case. By having that line, do you think case is still an issue? Thanks. "Dave Peterson" wrote: Just to add to Tim's response... Can you go to the offenders and have them run a small macro: Option Explicit sub testit() debug.print "****" & environ("username") & "****" end sub (the ****'s are just to help the username stand out more) Then look at the immediate window in the VBE and see if you notice the spelling error. I'm gonna bet that you still have a typing difference. =========== Or your code has an uppercase letter that you're missing. You can avoid that by: Select case lcase(...) case is = lcase("test1"), lcase("test2"), .... or add a line to the top of the module that will make all text comparisons non-case sensitive: Option Compare Text Please Help wrote: Hi Dave, Thanks for your response. Yes, everything is in lower case. In addition, there were no extra space or characters and transposed of characters. That is what puzzles me. Is there a limit on how many username can be used for "Username"? Thanks. "Dave Peterson" wrote: Are you typing the usernames in lowercase? Are you sure that there aren't extra space characters in the name string? I'd bet that there was a difference--maybe a couple of transposed characters??? Please Help wrote: Hello all, I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
Dave,
First of all, thank you very much for continuing to help. Secondly, I am pulling my hairs out. I sent them a blank workbook to populate their username as you instructed, and the populated usernames are exactly what I have in my code. The username is in between 4 ****s. No spaces or transpose of letters between ****s. The populated username begins with a capitalized letter and follows by lower case letters. I don't think that will be an issue since I have the code below. Am I correct? As far as lcase, the following line is in my original code: UName = LCase(Environ("UserName")) Thanks. "Dave Peterson" wrote: ps. And send those users with problems a workbook to open that populates a cell with their username. Tell them to save the file and return it to you. worksheets("sheet1").range("a1").value = "****" & environ("username") & "****" Maybe you'll see the difference. Dave Peterson wrote: I didn't notice that lcase() line in your original code. I'd still guess it was a difference in spelling, though. Please Help wrote: Good morning Dave, Unfortunately, the users are located in another state. I double-checked the usernames in my code and in our addressbook. They are the same. As far as case sensitivity, one of the lines in my code converts the usernames to lower case. By having that line, do you think case is still an issue? Thanks. "Dave Peterson" wrote: Just to add to Tim's response... Can you go to the offenders and have them run a small macro: Option Explicit sub testit() debug.print "****" & environ("username") & "****" end sub (the ****'s are just to help the username stand out more) Then look at the immediate window in the VBE and see if you notice the spelling error. I'm gonna bet that you still have a typing difference. =========== Or your code has an uppercase letter that you're missing. You can avoid that by: Select case lcase(...) case is = lcase("test1"), lcase("test2"), .... or add a line to the top of the module that will make all text comparisons non-case sensitive: Option Compare Text Please Help wrote: Hi Dave, Thanks for your response. Yes, everything is in lower case. In addition, there were no extra space or characters and transposed of characters. That is what puzzles me. Is there a limit on how many username can be used for "Username"? Thanks. "Dave Peterson" wrote: Are you typing the usernames in lowercase? Are you sure that there aren't extra space characters in the name string? I'd bet that there was a difference--maybe a couple of transposed characters??? Please Help wrote: Hello all, I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with "Username"
I missed the lcase() stuff in your other code (like I said earlier).
But you never shared what you actually used for the names in that "case is =" list statement. Maybe you typed uppercase characters in those strings. UName = LCase(Environ("UserName")) Select Case UName Case "User1", "user2", "user3", "user4", "user5",.....,"user19" Would make User1 never work. user1 is different from User1 So either fix your typing to use all lowercase or use: case is = lcase("user1"), lcase("user2"), .... or add Option Compare Text at the top of the module. Then your text comparisons in this module will be non-case-sensitive. Please Help wrote: Dave, First of all, thank you very much for continuing to help. Secondly, I am pulling my hairs out. I sent them a blank workbook to populate their username as you instructed, and the populated usernames are exactly what I have in my code. The username is in between 4 ****s. No spaces or transpose of letters between ****s. The populated username begins with a capitalized letter and follows by lower case letters. I don't think that will be an issue since I have the code below. Am I correct? As far as lcase, the following line is in my original code: UName = LCase(Environ("UserName")) Thanks. "Dave Peterson" wrote: ps. And send those users with problems a workbook to open that populates a cell with their username. Tell them to save the file and return it to you. worksheets("sheet1").range("a1").value = "****" & environ("username") & "****" Maybe you'll see the difference. Dave Peterson wrote: I didn't notice that lcase() line in your original code. I'd still guess it was a difference in spelling, though. Please Help wrote: Good morning Dave, Unfortunately, the users are located in another state. I double-checked the usernames in my code and in our addressbook. They are the same. As far as case sensitivity, one of the lines in my code converts the usernames to lower case. By having that line, do you think case is still an issue? Thanks. "Dave Peterson" wrote: Just to add to Tim's response... Can you go to the offenders and have them run a small macro: Option Explicit sub testit() debug.print "****" & environ("username") & "****" end sub (the ****'s are just to help the username stand out more) Then look at the immediate window in the VBE and see if you notice the spelling error. I'm gonna bet that you still have a typing difference. =========== Or your code has an uppercase letter that you're missing. You can avoid that by: Select case lcase(...) case is = lcase("test1"), lcase("test2"), .... or add a line to the top of the module that will make all text comparisons non-case sensitive: Option Compare Text Please Help wrote: Hi Dave, Thanks for your response. Yes, everything is in lower case. In addition, there were no extra space or characters and transposed of characters. That is what puzzles me. Is there a limit on how many username can be used for "Username"? Thanks. "Dave Peterson" wrote: Are you typing the usernames in lowercase? Are you sure that there aren't extra space characters in the name string? I'd bet that there was a difference--maybe a couple of transposed characters??? Please Help wrote: Hello all, I use the code below to validate the users who open the file. Somehow, the code is not recognizing two of the usernames. It keeps taking them to the Case Else, even though they are correct usernames. I even checked with my IT department to make sure that they are correct usernames. There are 19 authorized users. Does anyone know why? Thanks. Private Sub Auto_Open() Dim UName As String 'Use to check for authorized user UName = LCase(Environ("UserName")) Select Case UName Case "user1", "user2", "user3", "user4", "user5",.....,"user19" Case Else Workbooks("Workbook.xls").Windows(1).Visible = False MsgBox "Sorry, you are not one of the authorized users for " & _ "this file." vbCritical, "User Validator Macro" Workbooks("Workbook.xls").Close False End Select Worksheets("Sheet1").Activate End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"filename is locked for editing by username" | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Save active sheet as "filename" on a remote server with username and password | Excel Programming | |||
Environ("username") | Excel Programming | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |