Excel/UserForm Login


Good morning,
I found the code below in an Excel forum and was trying to adapt it to fit my needs.  

I have a UserForm
1: TextBox for Input Username (txtUsernameIn)
2: TextBox for Input Password (txtPasswordIn)
3) Submit Button (CommandButton1)

I have a Worksheet - "EmpDB".
ColA: Username (6 digit number)
ColB: Password
And this entire Range A:B, I have named "UserRegister"

I have code, attached to the CommandButton1 button on my UserForm. The purpose is to allow access to a veryhidden worksheet "ADMIN".

Each time I run the code an message box displays "Invalid UserName"(as programmed)indicating a user could not be found in the UserRegister   If I change the user name to alphabetic characters (ex "John" in colA) the code executes.

I'm using Excel 2010 in Windows7.

Private Sub CommandButton1_Click()
   Dim Username As String
   Username = txtUserNameIn.Text
   Dim password As String
   password = txtPasswordIn.Text
    'Check to see if data is entered into field: txtUserNameIn
   If IsNull(Me.txtUserNameIn) Or Me.txtUserNameIn = "" Then
       MsgBox "You must enter your username.", vbOKOnly, "Required Data"
       Exit Sub
   End If
    'Check to see if data is entered into field: txtPasswordIn
   If IsNull(Me.txtPasswordIn) Or Me.txtPasswordIn = "" Then
       MsgBox "You must enter your Password (case sensitive).", vbOKOnly, "Required Data"
       Exit Sub
   End If
    'Check to see if the Username & Password entered is a valid username in the 'User Register'
 On Error Resume Next
temp = WorksheetFunction.VLookup(Me.txtUserNameIn.Value, Range("UserRegister"), 1, 0)

If Username = temp Then
   temp = ""
   temp = WorksheetFunction.VLookup(Me.txtUserNameIn.Value, Range("UserRegister"), 2, 0)
   On Error GoTo 0
   If password = temp Then
       Sheets("ADMIN").Visible = xlSheetVisible
       MsgBox "Password & Username Accepted"
       Unload Me
       Sheets("ADMIN").Visible = xlVeryHidden
       MsgBox "Invalid Password"
       Unload Me
    End If
   Sheets("ADMIN").Visible = xlVeryHidden
   MsgBox "Invalid UserName"
   Unload Me
End If
End Sub

Format the username column as text; That will make the numbers APPEAR as text, ut they're still numbers. To make them really numbers, use a temporary parallel range which has the formula =A1&"". Then fill down, copy, pastespecial values onto col A. Now they're really text.
then change the vlookup to:
temp = WorksheetFunction.VLookup(cstr(Me.txtUserNameIn.Value), Range("UserRegister"), 1, 0)
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Bob Umlas


I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at http://www.thumbtack.com/ny/new-york/excel-training/


Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Excellence, The Expert, Microsoft

BA in math, Hofstra University, 1965

Awards and Honors
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 About.com. All rights reserved.