Login Form is one of the secure method to access the authorized pages, application and contents in any of the platform.
Suppose, you have developed any application or complex excel spreadsheet and you want to apply restriction so that only authorized users can access your application then this feature will be very useful.
In this post, we are going to learn how to create a login page for a spreadsheet or Excel based automated application.
Here, I am going to utilize and existing Data Entry Form. In this data entry form, we will add the feature of Login form so that whenever user will open this application, it will ask User Credentials to proceed with data entry job. If User will provide the correct login and password then it will open the application for further usage otherwise, it will get terminated.
Let us move to Visual Basic of Application window. To do that just click on Developer Tab then click on Visual Basic icon available in code group.
Let us insert a user form to design Login Window. To insert the form, just click on Insert Menu then click on user Form.
Design the form as per below image.
Set the properties of all the controls available in this form. Please follow the below given properties.
Now, we have done with designing part. Let’s start coding.
First of all, let us move to module window and declare a Global Variable to store the instances of invalid login attempt.
Public LoginInstance As Integer
Write the below code in code window of frmLogin Form.
Code to initialize the form controls on UserForm Initialize even.
Private Sub UserForm_Initialize() Me.txtUserID.Value = "" Me.txtPassword.Value = "" Me.txtUserID.SetFocus End Sub
Code on click event of cmdLogin command button.
Private Sub cmdLogin_Click() Dim user As String Dim password As String user = Me.txtUserID.Value password = Me.txtPassword.Value If (user = "admin" And password = "admin") Or (user = "user" And password = "user") Then Unload Me Application.Visible = True Else If LoginInstance < 3 Then MsgBox "Invalid login credentials. Please try again.", vbOKOnly + vbCritical, "Invalid Login Details" LoginInstance = LoginInstance + 1 Else MsgBox "You have exceeded the maximum number of login attempts.", vbOKOnly + vbCritical, "Invalid Credentials" Unload Me ThisWorkbook.Close Savechanges:=False Application.Visible = True LoginInstance = 0 End If End If End Sub
Code on click event of cmdClear command button.
Private Sub cmdClear_Click() Me.txtUserID.Value = "" Me.txtPassword.Value = "" Me.txtUserID.SetFocus 'Code to exit- Please remove the comments if you want to use it exit ' Unload Me ' ThisWorkbook.Close Savechanges:=False ' Application.Visible = True End Sub
Code to disable the close button available on frmLogin,
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = 0 Then Cancel = True End Sub
Now, we have done with all the required codes for frmLogin. Let us double click on ThisWorkbook in Project Explorer window to view the code window.
Write down the below code on Window Open events to initialize the global variable, hide the Excel Window and show the Login Form.
Private Sub Workbook_Open() LoginInstance = 0 Application.Visible = False frmLogin.Show End Sub
We have completed the coding. Let us save this file and open it for testing. Once you open it and enable the Macro, it will show Login Form to enter credentials.
Please watch our YouTube tutorial to learn how to create this Login form step by step.
Please click on below icon to download the Excel files used in this tutorial. (Use user name and password both ‘admin’ or ‘user’ to enter the credentials in login form and proceed with data entry process.)
Thanks for visiting our website! Please explore our site for more tutorial on Power BI, VBA and Excel. You can download several automated tools and templates.