Excel Userform Login – Multiple Level – Plus +++

 

Excel User form Login – Multiple Access Levels

Overview

This project will interlink with the next two projects. We will be creating a  Excel userform login with multiple level user access. This login will direct the user to the workbook navigation form(next project).

1. First we will create a user login
2. Second is a navigation form for the workbook
3. Third will be an administration form to set the visible sheets.

User login features

•  Login with 3 attempts
• Login requires Username and Pass code
• Pass code is hidden
• Login as Administrator
• All logins recorded
• Edit list of Users and Pass codes (any number allowed)
• Show current user on each worksheet

Video for Excel User form Login

Download the template

Template userform login check

 

Static named range

Login sheet cell K2 create a named range called [CurrentUser] no brackets

 

Formulas on the worksheets

Note: Paste these formulas into the formula bar after selecting the cell. Do not paste into the cell.

Place this formula on each sheet where you want the sheet name to appear

=MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256)&"  Worksheet"

Place this formula on the sheet where you want  the current users name to appear.

="Welcome Back        "& IF(CurrentUser="","",CurrentUser)

 

Setting up the login sheet

If you are using the template from Online PC learning then there is no set up required.

 

Creating the Login UserformIN ARTICLE ADS

Press Alt+F11 to access the VBA editor.

From the menu choose Insert / Userform

With the module selected Right Click and choose Properties and name the userform frmLogin.

In Properties select Caption and add “You have 3 attempts to login.”

Add three labels, one for the header and one each for the text boxes and add the text you see in the illustration below. This text will not be referred to in our code.

Add 2 text boses and a command button.

1. Name textbox1                     txtUser
2. Name textbox2                     txtPass
3. Name commandbutton1      cmdCheck and add a caption for this button called “Validate”
   

Your userform should look similar to the one below.

If you wish to add an image then insert and image control and locate the image. Note: make sure your image is formatted to a small size 10 -30 kb.

 

Insert 2 other Userforms

These 2 userforms will be the subject of the next 2 projects. We need them now so that they can be referenced in our code.  

1. frmNavigation   
2. frmSetup

Leave these 2 Userforms blank for now.

 

Adding Userform Code

1. Don’t let this long piece of code intimidate you.
2. It achieves 4 things:
3. Checks for an administrator name and passcode
   
4. Checks for a user name and password
5. Adds the user login and time to a database
6. Enters the user name to the worksheet
   

Note: At the top of the procedure we have declared the variable [Trial] Private Trial as Long. This will hold the number of attempts in memory. This is module level variable scope.

To access this area:
Right Click on the Userform and choose View Code.

Copy the code below and paste it into the userform code.

 Option Explicit
IN ARTICLE ADS Private Trial As Long
Private Sub cmdCheck_Click()
'Declare the variables
Dim AddData As Range
Dim user As Variant
Dim Code As Variant
Dim result As Integer
Dim TitleStr As String
Dim Current As Range
Dim PName As Variant
Dim msg As VbMsgBoxResult
'Variables
user = Me.txtUser.Value
Code = Me.txtPass.Value
TitleStr = "Password check"
result = 0
Set Current = Sheet2.Range("K2")
'Error handler
On Error GoTo errHandler:
'Destination location for login storage
Set AddData = Sheet2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
'Check the login and passcode for the administrator
If user = "Trevor" And Code = 9999 Then
MsgBox "Welcome Back: –   " & user & "   " & Code & vbCrLf _
& " You have admistrator priviledges" & vbCrLf _
& " I will open the control panel for you"
'record user login
AddData.Value = user
AddData.Offset(0, 1).Value = Now
'send the username to the worksheet
Current.Value = user
'unoad this form
Unload Me
'Show navigation form
frmSetup.Show
'End the procedure if conditions are meet
Exit Sub
End If
'Check user login with loop
If user <> "" And Code <> "" Then
For Each PName In Sheet2.Range("H2:H100")
'If PName = Code Then 'Use this for passcode text
If PName = CInt(Code) And PName.Offset(0, -1) = user Then ' Use this for passcode numbers only
MsgBox "Welcome Back: –   " & user & "   " & Code
'record user login
AddData.Value = user
AddData.Offset(0, 1).Value = Now
'Change variable if the condition is meet
result = 1
'Add usernmae to the worksheet
Current.Value = user
'Unload the form
Unload Me
'Show the navigation form
frmNavigation.Show
Exit Sub
End If
Next PName
End If
' Next UName
'Check to see if an error occurred
If result = 0 Then
'Increment error variable
Trial = Trial + 1
'Less then 3 error message
If Trial < 3 Then msg = MsgBox("Wrong password, please try again", vbExclamation + vbOKOnly, TitleStr)
Me.txtUser.SetFocus
'Last chance and close the workbook
If Trial = 3 Then
msg = MsgBox("Wrong password, the form will close…", vbCritical + vbOKOnly, TitleStr)
ActiveWorkbook.Close False
End If
End If
Exit Sub
'Error block
errHandler:
MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"
End Sub

This code will stop  the x button on the Userform from working.

Private Sub UserForm_QueryClose _
(Cancel As Integer, CloseMode As Integer)
'   Prevents use of the Close button
If CloseMode = vbFormControlMenu Then
MsgBox "Clicking the Close button does not work."
Cancel = True
End If
End Sub

Userform Login Code for the Module

Now we need to insert a module to hold a little bit of code.

Press Alt+F11 to access the VBA editor.

From the menu choose Insert / Module.

These 2 pieces of code will call the userforms.

 

Sub Showme()
frmLogin.Show
End Sub

Sub ShowNav()
frmNavigation.Show
End Sub

 

ThisWorkbook Code

In Project Explorer in the left double click on ThisWorkbook

This code will:

Select the Interface sheet

Hide the sheet tabs if they are visible

Call the Userform on the workbook open event

Private Sub Workbook_Open()
On Error Resume Next
Sheet1.Select
ActiveWindow.DisplayWorkbookTabs = False
Showme
On Error GoTo 0
End Sub

 

Test the project

Run a test with the workbook by closing and opening it.

Note: If you want to have passcode with text and numbers combined (1234thk) instead of only text then change this part of the macro. Text and numbers combined are treated as text.

If PName = CInt(Code) Then

to

If PName = Code Then

Note: The ranges for the number of users can be changed to dynamic named ranges or you can manually adjust the ranges.

Range("H2:H100") Adjust to suit or change to reflect new data ranges

Range("G2:G100")

To change this to a dynamic named range add for example a named range called “AllUsers” then change the code to Range("AllUsers")