Link to home
Start Free TrialLog in
Avatar of srikanthv2322

asked on

Prompt for Login Only Once In a VBA Form

I have Login code embedded in a Command Button in a VBA form, i want t store the login credentials provided by the user through Input Box and stop him from prompting each time he clicks on Command Button
In the VBA form, i have only 2 Command Buttons, one for Quering the result(where the login code is embedded) and other button to exit the file.

Please let me know where should i place the login code from prompting the user to enter the credentials each time.
Avatar of patrickab
Flag of United Kingdom of Great Britain and Northern Ireland image


Please upload your file.

Ok here is an example. However please note that Excel VBA is not suited for Login/Logout procedures. What I mean is it is not secure.

Please find attached an example on how to build your login screen.

All the Login Details are stored in sheet "Login" which is hidden.

The User ID's and Passwords to test are as follows

Admin      123456
User1      User1
User2      User2


Code Used

In Workbook Module

Private Sub Workbook_Open()
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Sheets("login").Range(strAddress) = "OFF"
    On Error GoTo 0
End Sub

Open in new window

In Module

Public CurrentSession As String
Public strAddress As String
Public strUser As String

Open in new window

In UserForm

Private Sub UserForm_Initialize()
     If CurrentSession <> "ON" Then
        Label1.Visible = True
        Label2.Visible = True
        TextBox1.Visible = True
        TextBox2.Visible = True
    End If
End Sub

Private Sub CommandButton1_Click()
     If CurrentSession = "ON" Then
        UserForm1.Caption = "Welcome " & strUser
        '~~> DO YOUR CODE
        '~~> Validate User
        Dim i As Long, lastRow As Long
        Dim strPass As String
        Dim acell As Range
        lastRow = Sheets("Login").Range("A" & Rows.Count).End(xlUp).Row
        strUser = Trim(TextBox1.Value)
        strPass = Trim(TextBox2.Value)
        For i = 1 To lastRow
            Set acell = Sheets("Login").Columns(1).Find(What:=strUser, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            If Not acell Is Nothing Then
                If acell.Offset(, 1).Value = strPass Then
                    acell.Offset(, 2).Value = "ON"
                    CurrentSession = "ON"
                    strAddress = acell.Offset(, 2).Address
                    MsgBox "Login Successful"
                    Unload Me
                    Exit Sub
                End If
            End If
        Next i
        MsgBox "Login UnSuccessful"
    End If
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Open in new window

Avatar of dlmille
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial