How To Create An Excel VBA Login Form

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.

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…

Wanna grab me a coffee? – Then Click This Green Box

If you find this article or videos really helpful, then consider grabbing me a coffee, it takes a lot of work and expense to keep this site going, the more caffine I have the harder I can work.

And if you do grab me a coffee (just click anywhere on this green area), thanks for your support 😉

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.

Wanna grab me a coffee? – Then Click This Green Box

If you find this article or videos really helpful, then consider grabbing me a coffee, it takes a lot of work and expense to keep this site going, the more caffine I have the harder I can work.

And if you do grab me a coffee (just click anywhere on this green area), thanks for your support 😉