Clever Geek Handbook

Excel vba username / password

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 have a custom excel format that I was working on and now I need to make it look more professional by having a login screen. I started with the code above, but I was stumped.

how to set it up my goal is to tell if id and password are the same, download the book or show the book and continue. the username and password are in the "User & Pass" sheet, the goal is to read from there in the a-user / b-pw columns respectively, and if that succeeds, I will hide this sheet so they cannot see other user information.

with what i started above, i just need to say this, if it matches usercolumn then the corresponding pw next to it will continue to still go to my error handler

I can do formatting about hiding and opening sheets, etc., just need help reading the username and pw

thanks in advance Z

Edited attempt:

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
+3


source to share


2 answers


TEST AND TRIP

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


Advice

Never let your user know (from a security standpoint) what went wrong - the username or password. Always show general message like "Unable to match user ID or password, please try again" :)

NTN

Sid

+3


source


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 all of your sheets are xlSheetVeryHidden from the start to combat disabled macros and hide them as part of your successful login. You will also want to set a password in your VBA project to prevent people from opening sheets. However, keep in mind that Excel is about as reliable as a wet paper bag;)

+1


source






More articles:


All Articles