Environ("UserName") changes from "User123" to "usera123"

[KyleJackMorrison]

Hello,
I have a code which unlocks a sheet based on usernames.


I've noticed that the code sometimes doesn't work because our Environ("UserName") changes from a Capital letter at the start to sometimes a lower case letter randomly.


For example,

"Morrisonk123" to "morrisonk123" This throws my code out and doesn't unlock the sheet as I have it set up to recognise the lowercase username.


Is there a way to get around this or do I have to put "Morrisonk123", "morrisonk123" just to be safe?

Sub xUnlock()
    Dim sUser As String
    Application.ScreenUpdating = False
    sUser = Environ("UserName")
    Select Case sUser
        Case "morrisonk102", "user1", "user2"
            Sheets("FOE Jan 19 - Dec 19").Unprotect Password:="11SQN4AMR"
            MsgBox "Welcome " & Application.UserName & " the FOE has now been unlocked for editing.", vbOKOnly, "Welcome"
        Case Else
            Sheets("FOE Jan 19 - Dec 19").Protect Password:="11SQN4AMR"
    End Select
    Application.ScreenUpdating = True
End Sub

 

[MoshiM]

You could use the ucase function on the first letter in combination with the Mid function and then put the password back together

 

[KyleJackMorrison]

Could you please elaborate or give me an example please?

 

[steve the fish]

You could use

Option Compare Text

at the top of the module or:

Select Case LCase(sUser)

 

[MoshiM]

Could you please elaborate or give me an example please?

Sub xUnlock()
    Dim sUser As String[COLOR=#b22222], First_Letter1 As String, First_Letter2 As String[/COLOR]
    
    Application.ScreenUpdating = False
    
    sUser = Environ("UserName")
    
[COLOR=#ff0000]    First_Letter1 = Mid(sUser, 1, 1) [/COLOR][COLOR=#008000]'returns a 1 character string starting at the first letter[/COLOR][COLOR=#ff0000][/COLOR]
[COLOR=#ff0000]    [/COLOR]
[COLOR=#ff0000]    First_Letter2 = UCase(First_Letter1) [/COLOR][COLOR=#006400]'a new variable will hold the ucase version[/COLOR]
    
[COLOR=#ff0000]    sUser = WorksheetFunction.Substitute(sUser, First_Letter1, First_Letter2, 1) [/COLOR][COLOR=#006400]'replace the first letter[/COLOR][COLOR=#ff0000][/COLOR]
    
    Select Case sUser


        Case "morrisonk102", "user1", "user2"
        
            Sheets("FOE Jan 19 - Dec 19").Unprotect Password:="11SQN4AMR"
            
            MsgBox "Welcome " & Application.UserName & " the FOE has now been unlocked for editing.", vbOKOnly, "Welcome"
        
        Case Else
        
            Sheets("FOE Jan 19 - Dec 19").Protect Password:="11SQN4AMR"
            
    End Select
    
    Application.ScreenUpdating = True
    
End Sub

 

[steve the fish]

Moshi why would you do all that when LCase (or UCase) works perfectly well to remove case sensitivity?

 

[MoshiM]

Your right. I over thought it.

[COLOR=#333333]Sub xUnlock()[/COLOR]    Dim sUser As String
    
    Application.ScreenUpdating = False
    
    sUser = lcase(Environ("UserName"))
        
    Select Case sUser


        Case "morrisonk102", "user1", "user2"
        
            Sheets("FOE Jan 19 - Dec 19").Unprotect Password:="11SQN4AMR"
            
            MsgBox "Welcome " & Application.UserName & " the FOE has now been unlocked for editing.", vbOKOnly, "Welcome"
        
        Case Else
        
            Sheets("FOE Jan 19 - Dec 19").Protect Password:="11SQN4AMR"
            
    End Select
    
    Application.ScreenUpdating = True
     [COLOR=#333333]End Sub[/COLOR]