Solved

Prompt for Login Only Once In a VBA Form

Posted on 2011-02-11
3
551 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
3 Comments
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
srikanthv2322,

Please upload your file.

Patrick
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now