Excel vba username/ password lookup

Question

Private Sub cmdLogin_Click()
On Error GoTo ErrorHandler

Dim RowNo As Long
Dim Id As String
Dim pw As String
Dim ws As Worksheets

Application.ScreenUpdating = False
Set ws = Worksheets("User&Pass")
Id = LCase(Me.txtLogin)


RowNo = Application.WorksheetFunction.Match(Id, ws.range("A2:A999"), 0)

CleanExit:
Set ws = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
Exit Sub

ErrorHandler:
MsgBox "Unable to match ID, enter valid ID.", vbOKOnly 
GoTo CleanExit

End Sub

I've got an excel userform i've been working on and now I need it to look more professional by having a log-in screen. I've started with the code above but I have come to a dead end.

how its set up my aim is to say if id & password matches then load up workbook or unhide the workbook and continue. the username and password are on a sheet called "User&Pass" Aim is it reads from there in columns a- user / b- pw respectively and if it's a success I will hide that sheet so they cant see other user's information

with what I started above I just need it to say if it matches usercolumn then corresponding pw next door to it continue else go to my errorhandler

i can do the formatting about hiding and unhiding sheets etc just need help with reading username and pw

thanks very much in advance Z

Editted attempt one;

Private Sub cmdLogin_Click()
On Error GoTo ErrorHandler
Dim RowNo As Long
Dim Id As String
Dim pw As String
Dim ws As Worksheets
Application.ScreenUpdating = False
Set ws = Worksheets("User&Pass")

Id = LCase(Me.txtLogin)
RowNo = Application.WorksheetFunction.Match(Id, ws.range("A2:A999"), 0)
RowNo = RowNo + 1
pw = ws.range("B" & RowNo)
If pw = Me.txtLogin Then
'continue
txt1.Value = "yes"
Else
GoTo ErrorHandler
End If


CleanExit:
Set ws = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
Exit Sub
ErrorHandler:
MsgBox "Unable to match ID, enter valid ID.", vbOKOnly
GoTo CleanExit
End Sub

@siddarthRout

Private Sub cmdLogin_Click()
Dim RowNo As Long
Dim Id As String, pw As String
Dim ws As Worksheet
Dim aCell As range
On Error GoTo ErrorHandler
Application.ScreenUpdating = True

Set ws = Worksheets("Details")
Id = LCase(Me.txtLogin)

Set aCell = ws.Columns(1).Find(What:=Id, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

'~~> If match found
If Not aCell Is Nothing Then
RowNo = aCell.Row
'~~> Rest of your code. For example if the password is
'~~> Stored in Col B then
Debug.Print aCell.Offset(, 1)
Unload Me
FrmMenu.Show
'~~> You can then use the above aCell.Offset(, 1) to
'~~> match the password which the user entered
Else '<~~ If not found
MsgBox "Unable to match ID, enter valid ID.", vbOKOnly
End If
CleanExit:
Set ws = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume CleanExit
End Sub

Quick side note: Is this intended to be a really secure password? Excel, especially done in this way, is very bad for that. — Gaffi
– Gaffi, Commented Mar 15, 2012 at 13:57
if theres a way to make it look professonal with username and password and secure doing it a different way I'm all ears and my code was an example of what I tried, just tried tweaking what you put in but no luck — Zenaphor
– Zenaphor, Commented Mar 15, 2012 at 13:59
Why not just have a list of authorised users then validate the logged in winows username against this? That way there's no need for a username and password form — SWa
– SWa, Commented Mar 15, 2012 at 14:01
@SiddharthRout Never heard of that route. :-) Mind linking to a tutorial? — Gaffi
– Gaffi, Commented Mar 15, 2012 at 14:55
@Gaffi: Sure :) Here is an example vbforums.com/showpost.php?p=3445523&postcount=6 In this example I am writing to a text file and in vb6 but the same concept applies to VBA and instead of writing to a text file, simply write to Excel Cell — Siddharth Rout
– Siddharth Rout, Commented Mar 15, 2012 at 14:57

Siddharth Rout · Accepted Answer · 2012-03-16 12:33:40Z

3

TESTED AND TRIED

Is this what you are trying?

CODE

Option Explicit

Private Sub cmdLogin_Click()
    Dim RowNo As Long
    Dim Id As String, pw As String
    Dim ws As Worksheet
    Dim aCell As Range

    On Error GoTo ErrorHandler

    If Len(Trim(txtLogin)) = 0 Then
        txtLogin.SetFocus
        MsgBox "Username cannot be empty"
        Exit Sub
    End If

    If Len(Trim(txtPassword)) = 0 Then
        txtPassword.SetFocus
        MsgBox "Password cannot be empty"
        Exit Sub
    End If

    Application.ScreenUpdating = False

    Set ws = Worksheets("User&Pass")
    Id = LCase(Me.txtLogin)

    Set aCell = ws.Columns(1).Find(What:=Id, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    '~~> If match found
    If Not aCell Is Nothing Then
        RowNo = aCell.Row
        If Me.txtPassword = aCell.Offset(, 1) Then
            FrmMenu.Show
            Unload Me
        Else
            MsgBox "Unable to match UserID or PasswordID, Please try again", vbOKOnly
        End If
    Else '<~~ If not found
        MsgBox "Unable to match UserID or PasswordID, Please try again", vbOKOnly
    End If
CleanExit:
    Set ws = Nothing
    Application.ScreenUpdating = True
    Exit Sub
ErrorHandler:
    MsgBox Err.Description
    Resume CleanExit
End Sub

TIP:

Never let your user know (from security perspective) what was incorrect - The username or the password. Always show a generic message like "Unable to match UserID or PasswordID, Please try again" :)

HTH

Sid

edited Mar 16, 2012 at 12:33

answered Mar 15, 2012 at 14:05

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Zenaphor Over a year ago

similar to above, prompts me to errorhandler instead of letting me unload progress, to test if it progress i told it to write a value of a textbox "yes" but it just send's me to errorhandler

Siddharth Rout Over a year ago

@Zenaphor: Please update your 1st post with the exact code that you are using :)

Zenaphor Over a year ago

I used what you put above, left it with the acell offset as it's whatever next to it

Siddharth Rout Over a year ago

@Zenaphor: Then where are you unloading the form? Also if you have included the "Exit Sub" then it would not show you the message in the error handler unless there is an actual error. In the above code depending upon the scenario, you will see three different kind of messages. 1) In Immediate window if a match is found 2) A message box "Unable to match ID, enter valid ID.", if no match is found 3) An error message if there is an error. For example There is no sheet by the name "User&Pass" or there is no textbox with the name txtLogin.

Zenaphor Over a year ago

I will give it another go when I'm back in the office then let you know

|

SWa · Accepted Answer · 2012-03-15 15:58:49Z

1

Another way

On Error Resume Next
If Me.password <> Application.VLookup(Me.username, Sheet1.Cells(1, 1).CurrentRegion, 2, False) Then
    MsgBox ("incorrect")
    Exit Sub
Else
    MsgBox ("Correct Password Entered")
End If

Also you will need to make sure that all your sheets are xlSheetVeryHidden from the outset to combat having macros disabled and un-hide them as part of your successful log in routine. You'll also want to set a password on your VBA project to prevent people unhiding the sheets. Bear in mind however, Excel is about as secure as a wet paper bag ;)

answered Mar 15, 2012 at 15:58

SWa

4,36325 silver badges42 bronze badges

1 Comment

Zenaphor Over a year ago

yes i thought all of the above, people won't try to break into it as it's a company tool, well will be, just so the managers think theres more control then there is

Collectives™ on Stack Overflow

Excel vba username/ password lookup

2 Answers 2

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related