How to make Login Form in Excel and VBA

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.