Excel/UserForm Login

Advertisement


Question
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"
       Me.txtUserNameIn.SetFocus
       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"
       Me.txtPasswordIn.SetFocus
       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
    '****************
   Err.Clear
   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").Select
       Range("A1").Select
   Else
       Sheets("ADMIN").Visible = xlVeryHidden
        
       MsgBox "Invalid Password"
       Unload Me
    End If
Else
   Sheets("ADMIN").Visible = xlVeryHidden
   MsgBox "Invalid UserName"
   Unload Me
End If
 
End Sub

Answer
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bob Umlas

Expertise

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/

Experience

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."

Publications
Excellence, The Expert, Microsoft

Education/Credentials
BA in math, Hofstra University, 1965

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

©2016 About.com. All rights reserved.