Convert Variable String to Lowercase in Excel VBA

Image 1

This is great for when you need to compare string values, from perhaps user generated infomation.  For this example I have taken it from a user log in screen.  I always forget whether a username or password is case sensative, and more importantly, did I use capitilisation anywhere within the user or password details…?  A very easy way to solve this problem is when the user logs in capture their log in and password and convert it it to a lowercase string for comparison against the saved log in info for that user.  You may be thinking why do this, well in Excel strings “Password” would be read completely different to “password”.

In this example I use the variables called LogInUsername & LogInPassword to capture the information the user enters into the log in box and password box respectively. I then convert this to lowercase using the the StrConv function in Excel VBA.  The code below is missing alot of other information that is irrelevent for this example.


LogInUsername = LogIn.LogInUsername.Value 'Captures log in name
LogInPassword = LogIn.LogInPassword.Value 'Captures password

If StrConv(Cells(X, 1), vbLowerCase) = LogInUsername And StrConv(Cells(X, 2),vbLowerCase) = LogInPassword Then

Call NextStage
Unload Me
Exit Sub
End If

Unload Me
End Sub

Image 2

The line of code in green is the part that converts the saved log in and password information for that user to lowercase.  So, StrConv(Cells(X, 1), vbLowerCase) = LogInUsername takes goes to the cell, in this instance X,1 and converts the username saved there to lowercase to then compare against the username the user has enterered to log in.  The same goes for the password section as well – StrConv(Cells(X, 2),vbLowerCase) = LogInPassword

This ensures that no matter where a user uses capitilsation I can ensure that it won’t cause problems with logging in.  So “Password” would read the same as “PASSWORD” and “PaSsWoRd”.  This line of code can be modified as well, simply replace the vbLowerCase with vbProperCase and it will capitilise the first letter of every word for you.