Even though Microsoft Excel doesn’t qualify as a high security data protection system, sometimes users of an workbook file must be sorted out and have different information access rights. The solution for this is to implement a login form in excel, like this one:
How to insert login form with password in excel
The first step is to create the form with all the fields, labels and buttons you need. Go to Developer tab -> Visual Basic
In the left panel you can right click the project, select Insert – UserForm and start building the form with the tools available in the Tool Box.
After this select the form and complete some of its attributes like the Name (let’s say frmPass), colors and all others that you need. Right click on the form and select View Code. Insert all the lines in the picture bellow:
Note that I have attached the excel file at the end of the article so that you don’t have to copy the code from the pictures, this is just for you to know the steps necessary.
The UserForm_QuerryClose procedure is needed in order for the form not to be closed from the X upper right button and the whole effort of building the login form be in vain.
Go to ThisWorkbook in the left side of the screen and add the code bellow:
This ensures that the form prepared for the log in is presented to the user when the excel file is opened. The Workbook_BeforeSave function ensures that when the file is saved, no information remains in the worksheet Report and the CA worksheet is hidden (very hidden – that is, it can’t be unhidden from excel but only from Visual Basic sheet attributes). These are all examples, you can choose to do whatever you need to, in order to protect all the worksheets and information.
Don’t forget to add a password to the whole project, in order for the users not to see the code behind. Right click on the project in Visual Basic and go to Protection tab:
I have attached the source excel file for you, so that you don’t have to retype the code in the pictures. The VBA password is PA$$ and the login form user is guest with pa$$ (the code behind the form transforms all the letters into caps to keep it simple).
You can find a sheet named Usr that is veryhidden, where I put a list of users and passwords that can be amended for your needs. I must say that the form can be bypass with Ctr+Pause. For this you must treat the error for Ctr+Pause so that it will not bypass the form. This is a subject for another article.
Download – Insert Login Form with Password Example
Enjoy!
Please leave a comment bellow, so I can understand if the article was useful or I have to update it or offer more details.
FIRST OF ALL THANKS…
CAN I USE MULTIPLE USER IN THIS FORM IF YES THEN PLS TELL ME HOW.
THIS IS EXCELLENT MODULE.
First of all, thank you very much for your kind support and appreciated efforts which is being provided over here. Excellent job.
Secondly, I’ve faced a problem after login through the form on my sheet, found that functions are not automatically working, needs to be re-updated manually.
Example
If my formula was
C1=sum (A1+B1),
Results will not appear automatically, until it will be updated manually every time I make a change. Hope you have an advice for me.
anyway, thank you for you great effort once again.
The file is set to Manual Calculation. Set it to Automatic from excel menu: Formulas/Calculation Options
Dear Ravindra,
You’re welcome!
In the cmdOK_Click() procedure you can insert a query from a database or, if you want to keep it static, you can add more users and passwords in this procedure:
if strconv(trim(……..)=”GUEST” or strconv(trim(…..)=”GUEST2″ then
………………..
if strconv(trim(……)=”PA$$” or strconv(trim(……..)=”PA$$2″ then
This is it!
Kind regards!
i cant login to access the code
do you have macros enabled? does the login form appear?
use credentials guest and pa$$
to view the VBA code use PA$$
Hello sir, it is very usefull. I want to change user id, how can we do that. I also couldn’t understand where to paste multiple user login codes. Please help
Okk. I found it. But it also allow “Guest1” with the Password of “Guest2” and vice versa. Any solution?
Hi Rajat,
Sorry for the late reply.
I amended and replaced the xlsm file, so now a list of users and passwords could be accommodated inside sheet Usr. Please open the VBA editor (PA$$) and set the visible attribute to Visible. When you save the file, this sheet will get VeryHidden so that it will not be available for anyone.
Regards!
I still couldn’t find ways to edit the user and password. Pls help
Hi Ramlee,
Be sure you have set the visibility property to “visible” for the Usr sheet in the VBA editor. There are 3 user defined.
Regards!
Dear Sir
Thank you so much for making this available (new to VBA), much appreciated. I need to restrict some sheets in the workbook, eg HR department can only see their HR sheet. Would it be possible for you to give me guidance how to add this to your template?
Kind Regards
Martie Snyders
Hi Martie,
You have to add department code to each user in the usr sheet (which is very hidden) and use this code in the login module:
Private Sub cmdOK_Click()
Dim j As Integer
j = 2
found = 0
While Sheets(“Usr”).Cells(j, 1) <> “” And found = 0
department = “”
If Sheets(“Usr”).Cells(j, 2) = Trim(txtName.Text) And Sheets(“Usr”).Cells(j, 3) = txtPassword.Text Then
found = 1
department = Sheets(“Usr”).Cells(j, 4)
Unload Me
End If
j = j + 1
Wend
If department = “HR” Then
Sheets(“HR”).Visible = xlSheetVisible
Else
Sheets(“HR”).Visible = xlSheetVeryHidden
End If
If found = 0 Then
MsgBox “Wrong user name or password!”, vbCritical, “Error”
txtName.SetFocus
End If
End Sub
What is the password for vba…. I want to edit
VBA password is PA$$
Thanks for providing this useful tool.
If security setting is macro disable, then this login form will not work. How to solve this problem?
Hi Steve,
Excel was not built to allow high security on worksheets. Because it is very easy to build viruses in VBA, Microsoft disables macros by default. You cannot change this setting by VBA code so you need to find ways to convince the user to activate macros.
All you can do is to hide all the sheets inside the file (very hidden property), except a welcome sheet, so that a user doesn’t see any data if he doesn’t enable macros and logs in with the right credentials. Set a password to VBA so that users cannot access VBA and cannot change the very hidden property of sheets. You must know that there are simple ways to break any such password. There are excel files on the internet which change VBA and sheet protection passwords using just VBA code.
Best regards!
The conclusion is that this login form code is working just for people that are not interested in breaking rules.
HI
IT’S VERY GOOD BUT I NEW LEARNER FOR VBA AND I CAN NOT OPEN the VBA editor (PA$$).SO PLEASE HELP ME
Hi Prakash,
I tested again the excel file on this article and VBA password (PA$$) works fine. How else can I help you?
Sir,
what means about j,1 and j,2 and j,3
is this any cell address in “user” sheet ?
Hi Shambhu,
As an example: Sheets(“Usr”).Cells(j, 2) refers to the value located on row j from the 2nd column in the Usr sheet. This sheet is VeryHidden.
Best reagrds!
Thanks for that beautiful work . I downloaded it fine . I am new in excel. Pls I neeed a code that allow different user to login to different form or sheet soay
Admin login to admin dashboard
Teacher login to teacher dashboard
Students login to student portal
Dear Jimoh,
I am glad you like it.
What you need looks like a full application and you shouldn’t use Excel for that kind of complex access filtering.
The security of excel passwords/locks is very poor. There are excel files available over the internet which break any excel password so you end up working in vain to filter access.
Sorry to disappoint you.
Kind regards!
You never disappointed me. In fact you give a direct answer suitable for my question. I am very grateful for that
Please don’t leave me in despair. I have been working on it since last year but I can’t get through. If not excess app what app will be suitable for that? How can you be of help to help me achieve this?
The idea is to build a school management system for my school as a computer teacher. So that teacher can enter the scores of their pupils and thereafter the admib print the result out to pupils.
Grateful for your concerns. More greese to your elbow
Thanks a lot
It looks like you need a web based application
Teachers can maintain lists with their pupils in the app and from time to time post exam grades or other info.
Every teacher can have access only to his own list.
Other users can have only the right to list and print the grades.
Even the parents or pupils may have access to their own grades or to the whole class info. Like an electronic school notice board.
What you need is an Apache server software installed on a server the school already have or maybe on any other desktop computer connected to the school’s network (which can stay turned on all the time – or during the day)
The software is open source and free of charge – available on the internet.
The advantages of this solution:
– all the users (teachers, admin, parents etc) can have access to the app on the same time through a classic web browser like internet explorer, mozilla, chrome etc
– the information is stored on the server for a long time and is better protected
– parents/pupils can see their historical grades
– custom reports can be requested from the database
– access using usernames, passwords and custom privileges for every user
– don’t need to send excel files over email
– a lot better protection from malicious intentions
Best regards!
But I don’t know his do I get the reply to my question that is why i posted it again. How do I read the reply.
Sorry for bothering you
Thanks very grateful
Now I can read the reply
Still waiting for the moderation
Thank you so much
Very grateful
Yes you got it. I need an apache and an open source as you observed bit I couldn’t ge a demo or free onet try first. Their price isn’t much bit I am trying to be careful of on line transaction where you release your bank details or atm info. I have installed netbeans ide 8.1 but to download a java app I think. Then I will a tutorial in PDF that will put me through the whole process.
In any way you can help . I will appreciate you please
Thanks for your understanding
Mr regards
Thank you so much for the code and file.
Hi I want to filter my data on the basis of user and he could see only those data