VBA Login Form

Excel VBA Login Form

It is possible to create a login based user form in excel VBA with all the login id drop-down list, and the password will be shared separately.  At some point in time, you must have got an idea of creating a password-based login user form, which requires the user to pick their user id and enter the password to access the required worksheet.

In this article, we will show you how to create a login userform using excel VBA.

VBA-Login

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Login Form (wallstreetmojo.com)

How to Create a Login User Form?

For example, assume you have region-wise sales numbers in different worksheets. We have 4 different zone names, and each zone worksheet has its related data only. Now the idea is to create a login form where the “East” zone sales head should see only “East” zone data, not any other zones, but as an admin, you should see all the zones worksheets.

You can download this VBA Login Excel Template here – VBA Login Excel Template

First, we need to insert a sheet name called “Admin.” In this admin sheet, we need to create a “Log in Id” and “Password” credentials.

Admin Sheet

I have named the zone and password the same you can change this later on. For zone names, I have created the name range as “ZoneList.” This “Name Manager” will be used later on this login userform.

Name Range

When the user opens the file, they should see one dummy sheet in the background, so create a new sheet and name it as “Dummy” Sheet.”

Add Dummy Sheet

Using these worksheets, we will create a login user form.

Step 1: Insert User Form

Press ALT + F11 key to open the VBA EditorVBA EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more window.

Open VBA Editor
  • From the “Insert” tab, insert “User Form.”
Insert Userform
  • This will create a new user form like the below one.
Userform
  • Press the F4 key to see the Properties window. From this properties window, change the name of the user form to “LoginUF”
vba log in - Change Userform Name
  • Similarly, using this properties window, we can play with the properties of the user form. I have made some of the property changes. You can refer to the below properties window to apply changes to properties of the user form.
vba log in - Userform Properties
  • Now my user form looks like this.
vba log in - Userform Edited

Step 2: Design Userform

  • From the toolbox of the userform, insert two label boxes and enter the text, as shown below.
vba log in - Add Labels
  • From the toolbox, insert “Combo Box.”
vba log in - Add ComboBox
  • For this combo box excel, we need to get the zone names from the worksheet “Admin Sheet,” so from the properties window of the “Combo Box,” first give a name to this combo box as “Zone_List_ComboBox” under “Name” property.
vba log in - Change ComboBox Name
  • From the “Row Source” property of the combo box, enter the name given to the zone list in the “Admin Sheet.”
Excel vba log in - ComboBox Add Row Source
Excel vba log in - ComboBox Drop-Down List
  • For “Enter You Password,” we need to insert a “Text Box” from the toolbox.
Excel vba log in - Insert TextBox
  • For this “Text Box,” we need to change the “Name” property and change it as “Password_TB.”
vba log in - Change TextBox Name

Now in the coding for the VBA login form, “Combo Box” will be referred by the name “Zone_List_ComboBox” and “Text Box” will be referred by the name “Password_TB.”

  • Insert two “Command Buttons” and enter the text as “Log in” and “Log Out.”
vba log in - Add CommandButton

For the “Log In” command button, change the name property to “Login_CommandButton,” and for the “Log Out” command button, change the name property to “LogOut_CommandButton.”

Step 3: Code

We are done with the VBA login user form design part. It’s time to write the code to create a login based user form in excel VBA.

  • Double click on the “Log In” Command Button. This will open a blank sub procedure like the below one.
vba log in - Log in Button

Inside this procedure, we need to write the code about what should happen if the “Log In” button is pressed.

I have already written the code. You can copy and paste the code from below inside the above procedure.

Code:

Private Sub Login_CommandButton_Click()

If Zone_List_ComboBox.Value = "" Then
MsgBox "Zone Cannot be Blank!!!", vbInformation, "Zone Name"
Exit Sub
End If

If Password_TB.Value = "" Then
MsgBox "Password Cannot be Blank!!!", vbInformation, "Password"
Exit Sub
End If

If Zone_List_ComboBox.Value = "Admin" And Password_TB.Value = "Admin" Then
Unload Me

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets
Ws.Visible = xlSheetVisible
Next Ws
Sheets("Admin").Select
Else

Dim ZoneName As String
Dim Password As Variant

ZoneName = Zone_List_ComboBox.Value
Password = Application.WorksheetFunction.VLookup(ZoneName, Sheets("Admin").Range("A:B"), 2, 0)

If Password <> Password_TB.Value Then
MsgBox "Password is not matching", vbInformation, "Wrong Password"
Exit Sub
End If

If Password = Password_TB.Value Then
Unload Me
Sheets(ZoneName).Visible = True
Sheets(ZoneName).Select
ActiveSheet.Range("A1").Select
End If
End If

End Sub

Similarly, double click on the “Log Out” command button and enter the below code.

Code:

Private Sub LogOut_CommandButton_Click()

        ThisWorkbook.Save
        ThisWorkbook.Close

End Sub
vba log in - Log out Button

Now double click on “User Form” (not on any of the buttons inserted) and add the below code.

Code:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
' Prevents use of the Close button
     If CloseMode = vbFormControlMenu Then
          MsgBox "Clicking the Close button does not work."
          Cancel = True
     End If
End Sub
Log in - Userform Code
  • Now double click on the “ThisWorkbook” icon. This will open up below the blank code field.
Thisworkbook

Now add the below code in this blank field.

Code:

Private Sub Workbook_Open()

     Dim Ws As Worksheet

     Application.DisplayAlerts = False
     Application.ScreenUpdating = False

     For Each Ws In ActiveWorkbook.Worksheets

     If Ws.Name <> "Dummy" Then
     Ws.Visible = xlSheetVeryHidden
     End If
     Next Ws

     LogInUF.Show

End Sub
Log in - Thisworkbook Code
  • We are done with the coding part of the form. Save the workbook as a “Macro-Enabled” workbook and reopen the workbook. Upon reopening the workbook, we should see the below window.
vba login form
  • From this user form, we need to choose the respective zone name and enter the associated password. For example, I will choose the “East” zone from the drop-down and enter the password.
vba login form for East
  • If I click on “Log In,” we can see only the “East” zone worksheet.
VBA Login East Zone Data

Similarly, if we log in with “Admin,” we can access all the worksheets.

VBA Login with Admin

Like this, we can create a login based password protected worksheet access.

Things to Remember

  • You need to use the same names that we have given to the user form, text box, and command button, and combo box in the coding.
  • You can change the zone name and password according to your wish.

This has been a guide to VBA Login. Here we discuss how to create a login form in excel VBA with the help of an example and downloadable excel sheet. You can learn more about VBA from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>