We’re going to create a general purpose Excel VBA login form, that can be used to display hidden worksheets to the end user of your application.
The principles used here can also be used to login to any application that you create or used to authenticate against a database such as Sql Server or Access.
Get the spreadsheet used in the tutorial video above.
Also get updates when new blog or video tutorials are released.
Step 1 Create the Form In The VBA Editor
The basics of creating an Excel vba user form are covered here in a previous article.
When created your form will look like this, design mode is left, run mode is right
Insert a useform from the vba editor, add two textboxes and two command buttons for cancel and login.
First ensure the properties window is visible.
Then set the following properties (to set each property, first click on the object, i.e. select it, then the relevant property set becomes available in the properties window.)
Userform =========== Name frmLogin Caption Login To Test System Height 129 Width 240 First Textbox ============= Name txtUserName Height 20 Left 12 Top 6 Width 204 Second Textbox =========== Name txtPassword Height 20 Left 12 Top 42 Width 204 PasswordChar *
First command button ======================= Name cmdCancel Cancel True Caption Cancel Height 20 Left 12 Top 72 Width 80 Second command button ======================= Name cmdLogin Default True Caption Login Height 20 Left 136 Top 72 Width 80
One final item is to set the "TabIndex" of the form controls, so that when the form loads the Username field will have focus – ready to type, then when you press Tab the password field gets focus, then the Cancel button and finally the login buttton, this creates a smooth user experience.
As in this demo simply select the Login button and set it’s TabIndex property to 0, then the Cancel Button to 0, then Password, then Username, you’ll notice, as you move along that the other tabindex properties adjust, meaning you’ll be left with… Username 0, Password 1, Cancel 2 and Login 3, and your tabbing will work nicely.
That’s the form designed, now it’s time for some code…
Create The Code Behind The Form
Let’s get into the code module then, with the form selected click the "View Code" button this will open the code module behind your login form.
This form’s going to be created using an object oriented methodology.
It’s going to communicate to it’s calling routine
The username is entered.
Then password entered and whether the user chose to cancel the process by clicking the cancel button.
It will thererfore have three "public" properties aaaUserName, aaaPassword and aaaCancelClicked (aaa because they sort to the top of the intellisense).
So let’s add the code to the form..
01 Option Explicit 02 03 Public aaaUserName As String 04 Public aaaPassword As String 05 Public aaaCancelClicked As Boolean 06 07 Private Sub cmdCancel_Click() 08 aaaCancelClicked = True 09 Me.Hide 10 End Sub 11 12 Private SubcmdLogin_Click() 13 ' Assign textbox values to properties above. 14 Me.aaaUserName = Me.txtUserName 15 Me.aaaPassword = Me.txtPassword 16 Me.Hide 17 End Sub
That’s the form done, if the user adds her username and password, the appropriate properties are set, when she clicks Login the form is hidden (note not destroyed) and it’s properties are available to the calling subroutine for examination.
Code That Uses The Login Form
The spreadsheet that goes with this article (yellow boxes) has a full working example of this, however so as not to make things too complicated, I’ll just show you the code to launch and test the login form here.
Insert an ordianary module to your project and add the following code (without the line numbers) or just view it in the downloadable spreadsheet. This function below is called from another routine, it returns False if the user fails the login test and True if he passes.
20 ' Default retun value of function is False 21 Private Function LoginUser(ByRef szUserName As String) As Boolean 22 Dim ofLogin As frmLogin 23 Dim szPassword As String 24 25 Set ofLogin = New frmLogin 26 ofLogin.Show vbModal ' Code stops here until user types login 27 szUserName = ofLogin.aaaUserName 28 szPassword = ofLogin.aaaPassword 29 ' Destroy login form to tidy up 30 Set ofLogin = Nothing 31 If userNameAndPasswordGoodToGo(szUserName, szPassword) Then 32 LoginUser = True 33 End If 34 End Function
To save time we also pass back the username to the calling routine via the function argument, this is useful when the user passes the login challenge but we need to know the userid of the authenticated user for later processing.
As mentioned earlier we’re using the form as an object so lines 22 and 25 create the form in memory.
Line 26 displays the form, but because we use vbModal as the argument, the code stops there until the user either choose Login or Cancel.
Lines 27 and 28 get the username and password from the form, at this point we no longer need the form as we’ve got our information from it so it’s Ok to "close" it. We do this by clearing the memory variable on line 30.
Line 31 is a custom function that validates the username and password, if the password is correct the code goes into line 32 where the return value of the function is set to True.
So lets quickly look at the code that calls the above LoginUser function.
01 Option Explicit 02 03 Sub SystemLogon() 04 Dim bResult As Boolean 05 Dim szUserName As String, szAllowedWorksheet As String 06 07 bResult = LoginUser(szUserName) 08 09 If bResult Then 10 ' Great we're good to go, code to allow access to system below 11 szAllowedWorksheet = getFoundRange(szUserName).Offset(, 2).Value 12 mShared.DisplayAllowedWorksheet szAllowedWorksheet 13 Else 14 ' Failed login, locked out of system 15 mShared.DisplayAllowedWorksheet "None" 16 MsgBox "Access Denied", vbExclamation, "BusinessProgrammer Application" 17 End Sub
Line 7 call the login function and gets a boolean True / False return.
If True, then user passed validation and the code enters the If clause on line 9 and gets access to the code on lines 10 through 12.
If login failed or user chose cancel then we go to the else part of the statement, lines 14 through 16
That’s pretty much it, if you want the spreadsheet with all the code sign up for the newsletter below and let me know in the comments below what problems you like to solve with VBA.
Good tutorial!
Interesting approach for using a form.
Thanks, Yes, this is the best way to use, you have so much more control when you use an Object-Oriented methodology,
How can I have it open more than one selected worksheet for different users?
It opens the necessary worksheet for each user on their machine, each user has their own login credentials.
Where is the file?
Opt into the yellow box in the article above, and you’ll receive download instructions.
Need the mutils code for this to work, no download option available for the workbook…. just wasted an hour of my life
I don’t know what you’re talking about John, I just opted in to the yellow box above, downloaded the workbook, it works and mUtils module is included in the VBA project.
That’s 10 minutes of my life I won’t get back 😉
If I have multiple users and I want to give them access to more than 1 sheet in my case some might need access to up to 10 sheets, how do I do that?
Hi Erick
I’d maintain a “very hidden” config sheet where each user login has a list of the sheets that they are allowed to access.
then when the user logs in, his user id is looked up and the sheet names that he’s allowed to view are put into an array for example.
a loop would first hide all the sheets, then unhide only the sheets the user is allowed to view.
Hi Sean,
Love your videos! Thank you so much for making them available.
You’re welcome Bill, Glad you like them.