report

Excel VBA - Guide To Create A Login Form

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
	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?

Did you give this a go and get it working?

See results

© 2014 Liam

Comments 14 comments

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

LiamBee 2 years ago from UK Author

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


Pratik07 profile image

Pratik07 9 months ago

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


pratik08 5 months ago

love it


Prafful 4 months ago

Thank You !


Luke Hanson 4 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.


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

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


Kris 3 months ago

How do you create more users?


Liam 3 months ago

@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 2 months ago

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


Liam 2 months ago

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


S M 7 weeks 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

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 7 weeks 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 3 weeks 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?

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article