Implement Error Handling Techniques With Excel VBA

Scenario

Let’s assume that we want to create a simple Excel VBA system where:

• There are different levels of users: Admin, Manager, and User.
• Each level will have different access rights (i.e., Admin has full access, Manager has limited access, and User has only viewing access).
• When a user opens the workbook, they are prompted to enter their username and password. Based on the entered credentials, the system determines their role and provides access accordingly.

Steps to Implement Multi-Level User Access Control

1. Define User Roles and Credentials

You can define a collection of users and their roles (Admin, Manager, User) and also store their credentials (username and password). A secure way to do this is to store the credentials and roles in a hidden sheet within the workbook.

For this example, we’ll assume we store the following details:

• Sheet name: UserRoles
• Columns: Username, Password, Role (Admin, Manager, User)

2. Create a Login Form

We need a login form where users can enter their username and password.

1. Go to Developer Tab > Insert > UserForm.
2. Create a simple form with:
   • A TextBox for the Username (Name it txtUsername).
   • A TextBox for the Password (Name it txtPassword and set the PasswordChar property to * to mask the password).
   • A CommandButton for login (Name it btnLogin).

3. Write the VBA Code for User Authentication

Here’s the code that checks the entered credentials against the stored data in the hidden sheet (UserRoles).

Dim UserRole As String
Sub ShowLoginForm()
    ' Show the login form
    LoginForm.Show
End Sub
Sub btnLogin_Click()
    Dim ws As Worksheet
    Dim username As String
    Dim password As String
    Dim lastRow As Long
    Dim i As Long
    Dim userFound As Boolean
    ' Get the username and password entered by the user
    username = LoginForm.txtUsername.Value
    password = LoginForm.txtPassword.Value
    ' Reference to the 'UserRoles' sheet where credentials are stored
    Set ws = ThisWorkbook.Sheets("UserRoles"
    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    userFound = False
    ' Loop through the UserRoles sheet to validate username and password
    For i = 2 To lastRow
        If ws.Cells(i, 1).Value = username And ws.Cells(i, 2).Value = password Then
            ' If a match is found, store the user role and set userFound to true
            UserRole = ws.Cells(i, 3).Value
            userFound = True
            Exit For
        End If
    Next i
    ' If no match found, show error message
    If Not userFound Then
        MsgBox "Invalid username or password", vbCritical
        Exit Sub
    End If
    ' Close the login form
    LoginForm.Hide
    ' Call the function to set user access based on role
    SetUserAccess UserRole
End Sub
Sub SetUserAccess(role As String)
    ' Based on the role, define what the user can access
    Select Case role
        Case "Admin"
            ' Full access (you can unhide sheets, allow editing)
            MsgBox "Welcome, Admin. You have full access."
            ' Example of unhiding a sheet
            ThisWorkbook.Sheets("AdminSheet").Visible = xlSheetVisible
        Case "Manager"
            ' Limited access (you can unhide some sheets or disable some features)
            MsgBox "Welcome, Manager. You have limited access."
            ' Example: Only allow editing certain areas
            ThisWorkbook.Sheets("ManagerSheet").Visible = xlSheetVisible
        Case "User"
            ' View-only access (you can hide sheets, disable editing, etc.)
            MsgBox "Welcome, User. You have view-only access."
            ' Example: Lock the sheet
            ThisWorkbook.Sheets("UserSheet").Visible = xlSheetVisible
            ThisWorkbook.Sheets("UserSheet").Protect
        Case Else
            MsgBox "Invalid role", vbCritical
    End Select
End Sub

Explanation of the Code:

1. LoginForm Show: This line will display the login form when the user presses the button to start the process.
2. btnLogin_Click: This subroutine checks if the username and password entered by the user match those stored in the UserRoles sheet. If they match, the user’s role is assigned to the UserRole variable, and the SetUserAccess subroutine is called to assign access rights based on the user’s role.
3. SetUserAccess: Based on the user’s role, this subroutine grants different levels of access. It can:
   • Show or hide sheets (e.g., for Admin, Manager, or User).
   • Protect sheets for view-only access (e.g., for Users).
   • Perform other custom actions like enabling or disabling features.

4. Configure Sheet Access Control

In the SetUserAccess procedure:

• Admin: The Admin gets full access, including unprotecting and making all sheets visible.
• Manager: The Manager might only get access to certain sheets or sections.
• User: The User might only have view-only access, where the sheets are protected, and editing is disabled.

5. Protect the Workbook and Sheets

To prevent users from easily changing the workbook structure:

• Protect Sheets: After setting up user access, you can protect sheets for non-Admin users so they cannot make changes.

ThisWorkbook.Sheets("UserSheet").Protect Password:="userpass"

• Protect Workbook: Protect the entire workbook structure to prevent unauthorized users from adding or deleting sheets.

ThisWorkbook.Protect Password:="workbookpassword"

6. Customizing the System

You can further customize this system by:

• Adding an Admin interface to manage users and roles (e.g., adding, deleting, or modifying users).
• Implementing more advanced permissions, like restricting access to certain ranges or data.

7. Adding an Admin Interface

If you’re an admin, you can add a User Interface for modifying user roles or resetting passwords. This can be done using forms or simple Excel sheets protected with VBA code.

Final Notes:

• Security: This method is a simple access control mechanism and should not be used for highly secure applications. Passwords in the Excel file are not encrypted and can be extracted by someone with sufficient knowledge of Excel.
• Extensibility: You can extend this model by adding more user roles, more specific access restrictions, and features like logging and auditing of user activity.