Updated date:

Excel VBA - Guide To Create A Login Form

Author:
excel-vba-create-a-basic-login-function

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.

excel-vba-create-a-basic-login-function

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
	Worksheets(1).Activate
	Login.Show
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?

This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.

Questions & Answers

Question: Can the Excel-VBA login creation process be modified so several users would have their own login?

Answer: Yes, you could grab the users windows login username for example and perform an IF statement to check if it's a particular user then expect this login password

Question: How would I go about grabbing the windows user login?

Answer: Application.UserName will return the username of the current logged in person.

Dim Var

Var = Application.UserName

The above would result in "Var" containing the username of the current logged in Windows user

© 2014 Liam

Comments

Liam (author) from UK on May 07, 2019:

@leigh

This happens if the workbook was closed on worksheet 1 as the activate event doesn't fire. You could add the code from worksheet activate to workbook open too to get your desired result

leigh on May 07, 2019:

Thanks it works perfectly! One question though I want my login worksheet to open with the login form how do i go about doing that? when i open it the login form won't open until i change worksheets and go back to that sheet.

Liam (author) from UK on May 07, 2019:

Make sure you have your "Private Sub LoginButton_Click()" configured correctly- part of step 3 in the guide

leigh on May 07, 2019:

when i click my login button it's not doing anything?

javad on December 23, 2018:

very good,thankul

Bilal Yousaf on December 06, 2018:

Hi There,

I am new on VBA so I am not getting it that way.

Can i have a .xlsx file of this article at bilal.yousaf@sapphiretextiles.com.pk

Thanks

Bilal Yousaf

5555 on December 02, 2018:

hi. could anyone explain on how to create a login form where we could get the data that already exist in the database excel appear in another form? if anyone get what i mean, please teach me. Thankyou.

Liam (author) from UK on November 02, 2018:

@Andy, what isn't working? If you explain then maybe you could get some help with your error

andy on November 02, 2018:

not working yet

Liam (author) from UK on October 31, 2018:

Hi @milland

Change:

Private Sub Workbook_Open()

LoginFlag = False

End Sub

To:

Private Sub Workbook_Open()

LoginFlag = False

Worksheets(1). activate

End Sub

See if that helps

Milind P on October 31, 2018:

Dear Liam, First of all thank you very much for the article on "Excel VBA - Guide To Create A Login Form". Its very helpful. I have followed your steps and have found it to be working satisfactorily. The only hitch is when I leave the workbook on some worksheet ( saving before closing ) which actually was locked behind Login, but when re-opened the workbook, that particular worksheet can be viewed, edited etc...please suggest solution for this.

Liam (author) from UK on September 07, 2018:

@Harshita

https://owlcation.com/stem/Excel-VBA-Restrict-Work...

Use this code but on the worksheets you want to restrict to certain users (worksheet_activate instead of workbook_open). You'll need to know their windows username. You could use this code in conjunction with the code for login on this page, or simply on its own for auto-login.

The sheets you don't want to hide from anyone simply need no code

Harshita on September 06, 2018:

Thanka a lot for sharing this.

I need one help.. I have made a woorkbook consisting of 7 sheets, out of which 3 sheets has data which need not be hidden.

Other 4 sheets is named as per team members name (Name1, Name2, Name3, Name4).

I wanted to know the code which would allow team members to edit only their named sheet and no one elses and can also see at the same time non-hidden sheets (3 sheets).

Liam (author) from UK on July 16, 2018:

You could put some code in the Workbook Open event that checks the windows login of the user and if its a particular user, hide specific sheets from the user. (Remember to use the "veryhidden" property, else they can simply unhide them)

ajit on July 13, 2018:

is that possible to make protect multiple sheet with different password in Excel. i am facing problem with share worksheet. i want to protect view from one user to another user. please advise and mail me if possible @

abeajit_1987@yahoo.com

Derek Lore on July 12, 2018:

This should not be used to achieve any sort of actual security because since all of the user info is stored unencrypted in a VBA macro, it will be visible in the vbaProject.bin file contained within the .xlsm file in plain text. To protect against this, the passwords should be stored encrypted with a salt and then user entries should be encrypted and salted to check against the stored value.

chirag prajapati on January 21, 2018:

how i can give different rights to different users like read write?

Liam on November 22, 2017:

Hi @Arturo, you're correct that if macros are disabled then the system won't work as intended. You could get around this by making the sheet "VeryHidden" with VBA when logged out or workbook closed. Then unhiding the sheet on successful login

Make sure the sheet is "VeryHidden" when saved and shared for the first time

Arturo on November 22, 2017:

Hi:

The macro works fine, but I found a very grave issue with it:

If your excel has the macros disabled by default (which, by the way, it is in most cases), all the information on the protected sheets is available, to be seen by anyone

Liam (author) from UK on October 11, 2017:

@MarkC

Ensure that you are not calling "Login.Show" in more than one place (for example, on workbook open and worksheet activate)

Mark C on October 11, 2017:

I have added this code to an Excel project I have been working on and it has worked amazingly.

I only have 1 issue with it and that is I have to log in twice in order to access my workbook.

I have looked into the code but cannot understand why it would be asking for this. Any suggestions?

Liam on August 28, 2017:

@Marcin

I can't see anything wrong with your code. Sounds like you don't need this part of the code:

Private Sub Worksheet_Activate()

If LoginFlag = False Then

Worksheets(1).Activate

Login.Show

End If

End Sub

This is what ensures no other part of the Excel worksheet can be seen without login. Try changing the "Login.Show" to be inside a button press, this allows the user to decide when to login, not when the sheet opens.

Marcin on August 24, 2017:

It does not work, the window keeps appearing. Just to be clear below is my code - can you see any issue with it?

Private Sub LoginButton_Click()

If Me.UsernameBox.Value = "admin" Then

If Me.PasswordBox.Value = "admin" Then

LoginFlag = True

Unload Me

Exit Sub

End If

End If

MsgBox "Sorry, Incorrect Login Details"

Unload Me

End Sub

Private Sub QuitButton_Click()

Unload Me

End Sub

Liam Bee on August 23, 2017:

Try putting "Unload Me" after the message for unsuccessful login

Marcin on August 23, 2017:

I have a problem with the Login Window - if I put incorrect data, the message appears as expected, but there is no way to get rid of the Login Window. The only way to get rid of it is to put the correct data. If I try to close it with X it keeps appearing. I even created Quit button with Unload Me or Hide Me, but it did not really resolve the issue. Any ideas? Thanks!

amateur on June 16, 2017:

The code works if I use F5 to run it in VBA but the login page doesn't popup when I open excel. Please help.

Moses Simukonde on May 17, 2017:

how can i allow the other users to see selected sheets and to have access to all 10 sheets

sostrax on April 26, 2017:

Just a helpful hint. You should note/change the line to use case insensitivity like so:

If UCase(Me.Username.Value) = UCase("Admin") Then

Which will accept:

Admin, admin, AdMin, etc....

Solomon Kinyanjui on January 13, 2017:

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

Hemant on December 26, 2016:

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

Terry on November 09, 2016:

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?

Liam on October 13, 2016:

@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

S M on October 13, 2016:

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

Sheets("Sheet1").Select

Range("A1").Select

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 on September 07, 2016:

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

Kris on September 07, 2016:

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

Liam on September 06, 2016:

@Kris

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 on September 06, 2016:

How do you create more users?

Luke Hanson on August 06, 2016:

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.

Prafful on July 19, 2016:

Thank You !

pratik08 on June 29, 2016:

love it

Pratik07 on February 22, 2016:

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

Liam (author) from UK on August 24, 2014:

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

Nesbyte from UK on August 24, 2014:

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