Solved

Prompt for Login Only Once In a VBA Form

Posted on 2011-02-11
3
575 Views
Last Modified: 2012-05-11
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
Comment
Question by:srikanthv2322
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 34878015
srikanthv2322,

Please upload your file.

Patrick
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34878033
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
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34880282
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question