• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 614
  • Last Modified:

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.
0
srikanthv2322
Asked:
srikanthv2322
1 Solution
 
patrickabCommented:
srikanthv2322,

Please upload your file.

Patrick
0
 
SiddharthRoutCommented:
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

Sid

Code Used

In Workbook Module

Private Sub Workbook_Open()
    UserForm1.Show
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
      Else
        '~~> 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


Login-Example.xls
0
 
dlmilleCommented:
Simple solution

>>Please let me know where should i place the login code from prompting the user to enter the credentials each time.

Declare a global variable in one of your modules called alreadyLoggedin.  When the workbook is opened, we can set that to false.  After clicking a button to do what you want to do, you'll test for alreadyLoggedin.  If false, then get credentials, and set alreadyLoggedin to TRUE.  After that, it would not prompt the user again, until workbook is closed...

 
'THE BELOW WOULD BE PART OF YOUR CLICK BUTTON CODE IN SHEET CODEPAGE

Private Sub CommandButton1_Click()
    Call logInandGetData
End Sub

'THE BELOW GOES IN A MODULE
Public alreadyLoggedIn

Sub logInandGetData()

    If alreadyLoggedIn Then
        MsgBox "already logged in so getting data"
        'get data code here
    Else
        MsgBox "now prompting you for user credentials"
        'use credentials to get data
        'after successful login, use this command
        alreadyLoggedIn = True
    End If
    
End Sub

Open in new window


See attached.

Dave
GetUserCredentialsOnlyOnce-r1.xlsm
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now