Cell PhonesComputersConsumer ElectronicsGraphic Design & Video EditingHome Theater & AudioIndustrial TechnologyInternet

Excel VBA - Guide To Create A Login Form

Updated on October 14, 2016

Joined: 2 years agoFollowers: 1Articles: 5

VBA Login Area Explained

Many people use Excel every day and share their workbooks with other users. One of the frustrating parts of sharing is that other people can modify, edit or delete your data. By creating a Login area, you can control which sheets are available for view to anyone who is not logged in. This comes in handy in many applications, such as management systems, where instead of multiple workbooks to keep information (such as budgets) away from less senior members of staff, you simply lock it away behind a Login option.

Excel has it's limitations and an experienced user of VBA and Microsoft systems may be able to bypass this Login, however, for the average user it does more than suffice.

VBA (Visual Basic for Applications) allows users of Microsoft applications to create custom code and utilise it to perform actions otherwise not included. So let's get started

Step 1 - Accessing VBA And Creating A Form

VBA can be accessed in one of two ways:

  1. Simply press ALT + F11
  2. Head to the Options and select "View Developer Tab" then click Visual Basic (2007 Onwards)

When the editor opens, you'll be presented with a grey window with a project manager down the left hand side.

Project Manager - This is where you move between your workbook sheets, forms and modules to view and edit code.

For now, right click on "VBAProject(Book1)" (or whatever your workbook is called) and select "Insert" and then "Userform"

You'll be presented with a blank Form. If the toolbox does not open automatically, click the icon on the toolbar that represents a Spanner and a Hammer. This toolbox has everything you need to create the Login screen.

Step 2 - Adding Items To The Form

Now you have your newly created form we need to set it up to look like a Login form. Follow the below steps to end up with the result on the right:

  1. In the Properties window (bottom left) you'll see "Name" field, change this to "Login"
  2. Notice that the UserForm title still says "Userform1"? You'll also need to edit the "Caption" field to something like "Please Login"
  3. From the toolbox, select the "A" button and place a label in the top left corner of the UserForm. You'll need to edit the label to read "Username". You can do this by either clicking once, waiting 2 seconds and then clicking again (Double clicking will take you to the code screen for that label) or editing the Caption in the Properties window
  4. Resize the label to fit the text, otherwise it can overlap other items
  5. From the toolbox, select the "AB|" button and place a Textbox underneath the "Username" label.
  6. With the new Textbox selected, edit the "Name" field to "Username". This name is used to reference the Textbox in the code we'll write later
  7. Repeat steps 2 - 6 for the "Password" field and label ensuring the Textbox is named "Password"
  8. With the "Password" Textbox selected, find the "PasswordChar" property from the Properties window. Place a "*" in this field, this means that characters typed are hidden, like a standard password field
  9. From the toolbox, click on the Command Button icon (looks like a grey rectangle) and draw a button below the password field. Change the name of the command button from "CommandButton1" to "LoginButton" and the caption to "Login"
  10. You can now resize the entire Userform to fit the items placed on it like in the example.

That's it, the form is created, now we need to add the code behind it to tell it what to do with the information entered on it.

Step 3 - Adding VBA Code

This is the bit that most people don't understand, so rather than simply telling you to copy and paste something, it's much better that you follow the below steps and understand how the code process works.

In this guide, we'll set up the username to be "Admin" and the password to be "1234"

1. In the editor window, double click on the Login button you created earlier. The view will change to "Code View" and will display:

Private Sub LoginButton_Click()

End Sub

All of your code for the button must be written between these lines.

2. We need to tell Exel how to check the username matches "Admin", to do this we need to write:

If Me.Username.Value = "Admin" Then

This line tells Excel to check that the value in the textbox called "Username" is equal to "Admin".

Underneath, write:

If Me.Password.Value = "1234" Then

This line tells Excel to check that the value in the textbox called "Password" is equal to "1234".

The two lines together will check that all Login information is correct, now we need to tell VBA what to do if that is the case, underneath write:

LoginFlag = True

This will set a Login flag that we can check against when opening sheets, however it is not set up yet, we'll cover that in a second. Now we need to tell Excel to stop checking for the values and what to do if they are not the correct values, below the LoginFlag line, write:

		Unload Me

		Exit Sub

	End If

End If

MsgBox "Sorry, Incorrect Login Details"

The "Unload Me" statement makes sure the form closes on a successful login. The "Exit Sub" line will terminate the rest of the code if the LoginFlag is set to true, the two "End If" statements will tell Excel to stop checking the relevant IF statements set earlier. The "MsgBox" statement will load a pop up box if the Login is not successful.

In all, your code should look like this:

Private Sub LoginButton_Click()

If Me.Username.Value = "Admin" Then
	If Me.Password.Value = "1234" Then
		LoginFlag = True
		Unload Me
		Exit Sub
	End If
End If

MsgBox "Sorry, Incorrect Login Details"

So what about the LoginFlag? We need to set this up so that it always remembers the last state. VBA instantly resets all variables when the code is terminated so we need to tell it to remember.

Right click in the project window and select "Insert" and then "Module" (Not class module!) and copy the following in to it:

Global LoginFlag As Boolean

The "Global" tells Excel to remember the variable even after the code finishes executing and allow the entire program to access the value stored, the "Boolean" simply tells Excel that this variable is either True or False.

Now all we need to do is tell the sheets we want to protect to check that the "LoginFlag" is true and to reset the flag when opening the workbook.

Step 4 - Protecting Sheets And Resetting Login

First off, create a new sheet if you don't already have more than one.

Open the VBA editor again (Press ALT + F11) and in the Project Window open the code for Sheet 2 and write:

Private Sub Worksheet_Activate()

If LoginFlag = False Then
End If

End Sub

This code checks the if the LoginFlag is False, if it is it opens the first sheet in the workbook and opens the Login form for the user to log in. (Note - You cannot use this code on sheet 1!)

That's it, you can copy that code to any sheet you wish to protect in the VBA editor.

All that's left is to reset the LoginFlag when the workbook is opened (so that if it is saved with the LoginFlag set to True, it'll be returned to Flase). In the Project Window, open the item called "ThisWorkbook" and write:

Private Sub Workbook_Open()
LoginFlag = False
End Sub

That's it, you now have a fully functioning Login system to protect your sheets from view and edit.. However, people can still delete, rename and tamper in other ways! In the future look out for guides to stop this from happening too.

How'd You Find This?

Did you give this a go and get it working?

See results

© 2014 Liam


    0 of 8192 characters used
    Post Comment

    • Nesbyte profile image

      Nesbyte 2 years ago from UK

      Great hub, very interesting. I didn't know you could add VBA code to a spreadsheet.

    • LiamBee profile image

      Liam 2 years ago from UK

      Thanks, it's my first hub of many Excel and VBA hubs (I Hope!) :)

    • Pratik07 profile image

      Pratik07 10 months ago

      Thanks, It's Really Working ..... For Login Page Static

    • pratik08 6 months ago

      love it

    • Prafful 6 months ago

      Thank You !

    • Luke Hanson 5 months ago

      Hi, great article. I would like to ask, how can we track the number of logins. For example say we have a new sheet and on this sheet their are four columns, Username , login date, login time, unsuccessful login attempts. How do we connect the login form with this sheet to gather the aforementioned data. I would greatly appreciate any help.

    • दिलीप कुमार पटेल 5 months ago

      मैं चाहता हूँ कि एक्सेल ओपेन होने से पहले एक लॉगिन विंडोज ओपेन हो जिसमे मैं पासवर्ड एंटर कर लॉगिन करें इसकेलिए क्या करना होगा ?

    • Kris 4 months ago

      How do you create more users?

    • Liam 4 months ago


      If Me.Username.Value = "Admin" Then

      If Me.Password.Value = "1234" Then

      LoginFlag = True

      Unload Me

      Exit Sub

      End If

      End If

      If Me.Username.Value = "NEWUSERNAME" Then

      If Me.Password.Value = "5678" Then

      LoginFlag = True

      Unload Me

      Exit Sub

      End If

      End If

    • Kris 4 months ago

      Can you link those users to certain tabs instead of the entire workbook? Sorry I not good with VBA.

    • Liam 4 months ago

      Yes, just put the code in only the sheet you want them to see.

    • S M 3 months ago

      That was really useful, thank you. Only thing you missed is, if the spreadsheet is saved with a password protected sheet open, it will remain open when you go back into the document later on and not prompt you for a password. I suggest adding



      to your workbook code if "sheet1" is unprotected and "sheet2" is the protected one so when the workbook is opened it will always start up on "sheet1"

    • Liam 3 months ago

      @SM step 4 covers the scenario you are describing. if the user saves the workbook with a protected sheet open, the next time the workbook is opened, the login flag will be false, so the code will return to sheet 1

    • Terry 2 months ago

      Liam, I am receiving Compile Error: Method or data member not found and it highlights .Username.Value portion of the line If Me.Username.Value = "Admin" Then. Thoughts please?

    • Hemant 2 weeks ago

      Really Useful My 1st VB Form is successful ...yeeeeeeee

    • Solomon Kinyanjui 2 days ago

      Hi I would like you to help with a vba code which can change password and username on userform interface without going directly to the macros to change. I mean the userform should delete old password and username and create the new password and username

    Click to Rate This Article