Insert login form with password in excel Download

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:

Login form in excel

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.

Form_View_Code

 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:

Workbook open code

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:

project_protection

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.

 

21 comments to “Insert login form with password in excel Download”

You can leave a reply or Trackback this post.
    • Ahmed Mohsen says: May 15, 2018 at 10:31

      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

         
  1. 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!

     
    • do you have macros enabled? does the login form appear?
      use credentials guest and pa$$

      to view the VBA code use PA$$

       
  2. Rajat Gandhi says: November 3, 2017 at 12:11

    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

     
  3. Rajat Gandhi says: November 3, 2017 at 12:57

    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!

       
    • 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!

       
  4. Martie Snyders says: January 22, 2018 at 11:21

    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

       
  5. Steve Steve says: June 8, 2018 at 23:46

    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.

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

       
    • 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!

       

Write a Reply or Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.