Excel VBA - Guide To Create A Login Form

Updated on October 14, 2016

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

    0 of 8192 characters used
    Post Comment

    • LiamBee profile imageAUTHOR

      Liam 

      2 weeks ago from UK

      @Harshita

      https://hubpages.com/technology/Excel-VBA-Restrict...

      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

    • profile image

      Harshita 

      2 weeks ago

      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).

    • LiamBee profile imageAUTHOR

      Liam 

      2 months ago from UK

      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)

    • profile image

      ajit 

      2 months ago

      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

    • profile image

      Derek Lore 

      2 months ago

      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.

    • profile image

      chirag prajapati 

      8 months ago

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

    • profile image

      Liam 

      10 months ago

      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

    • profile image

      Arturo 

      10 months ago

      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

    • LiamBee profile imageAUTHOR

      Liam 

      11 months ago from UK

      @MarkC

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

    • profile image

      Mark C 

      11 months ago

      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?

    • profile image

      Liam 

      13 months ago

      @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.

    • profile image

      Marcin 

      13 months ago

      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

    • profile image

      Liam Bee 

      13 months ago

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

    • profile image

      Marcin 

      13 months ago

      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!

    • profile image

      amateur 

      15 months ago

      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.

    • profile image

      Moses Simukonde 

      16 months ago

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

    • profile image

      sostrax 

      17 months ago

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

    • profile image

      Solomon Kinyanjui 

      20 months ago

      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

    • profile image

      Hemant 

      21 months ago

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

    • profile image

      Terry 

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

    • profile image

      Liam 

      23 months 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

    • profile image

      S M 

      23 months 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"

    • profile image

      Liam 

      2 years ago

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

    • profile image

      Kris 

      2 years ago

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

    • profile image

      Liam 

      2 years 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

    • profile image

      Kris 

      2 years ago

      How do you create more users?

    • profile image

      Luke Hanson 

      2 years 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.

    • profile image

      Prafful 

      2 years ago

      Thank You !

    • profile image

      pratik08 

      2 years ago

      love it

    • Pratik07 profile image

      Pratik07 

      2 years ago

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

    • LiamBee profile imageAUTHOR

      Liam 

      4 years ago from UK

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

    • Nesbyte profile image

      Nesbyte 

      4 years ago from UK

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

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)