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:
- Simply press ALT + F11
- 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:
- In the Properties window (bottom left) you'll see "Name" field, change this to "Login"
- Notice that the UserForm title still says "Userform1"? You'll also need to edit the "Caption" field to something like "Please Login"
- 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
- Resize the label to fit the text, otherwise it can overlap other items
- From the toolbox, select the "AB|" button and place a Textbox underneath the "Username" label.
- 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
- Repeat steps 2 - 6 for the "Password" field and label ensuring the Textbox is named "Password"
- 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
- 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"
- 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".
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?
© 2014 Liam