Implement Real-Time Data Monitoring Tools With Excel VBA

1. Overview of User Authentication System

A typical User Authentication System consists of:

• A login form where users enter their credentials (username and password).
• A way to validate those credentials against a stored set (e.g., a database or a list in Excel).
• Restricted access to certain features or areas of the workbook based on successful authentication.

2. Preparing the Excel Workbook

Before starting the code, we will set up a simple system:

1. Sheet1 – This will contain a list of usernames and passwords.
   • In Sheet1, create a table with the following columns: Username and Password.
   • You can populate the table with a few usernames and their corresponding passwords for testing purposes.

Example:

| Username  | Password  |

|———–|———–|

| admin     | admin123  |

| user1     | password1 |

| user2     | password2 |

1. Sheet2 – This will be a protected sheet where only authenticated users can access certain information or functionality.

3. Creating the User Authentication Form

We’ll create a basic login form with two fields: Username and Password.

Steps:

1. Open the VBA editor by pressing ALT + F11.
2. In the editor, go to Insert > UserForm. A new form will appear.
3. Add the following controls to the form:
   • Two TextBox controls (TextBox1 for Username and TextBox2 for Password).
   • Two Label controls (for Username and Password).
   • A CommandButton (for logging in).
   • Optionally, a Label for displaying messages (e.g., Label3 to show errors).

The UserForm should look something like this:

• Username [TextBox1]
• Password [TextBox2] (set PasswordChar property to * to hide input)
• Login [CommandButton]

4. Writing the VBA Code for Authentication

Now that we have the form, we’ll write the code to check the username and password entered against the data in Sheet1.

Code Explanation:

1. Validate User Login: We’ll loop through the list of usernames and passwords stored in Sheet1 and compare them with the entered username and password. If a match is found, we’ll authenticate the user and allow access. If not, we’ll display an error message.
2. Code for User Authentication: Below is the VBA code that will go into the CommandButton click event (the login button) for authentication.

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim username As String
    Dim password As String
    Dim i As Long
    Dim validUser As Boolean
    ' Retrieve username and password entered in the form
    username = TextBox1.Value
    password = TextBox2.Value
    ' Check if username and password fields are filled
    If username = "" Or password = "" Then
        MsgBox "Please enter both username and password.", vbExclamation
        Exit Sub
    End If
    ' Set reference to the worksheet where users are stored (Sheet1)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Find the last row with data in the worksheet (for user list)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Initialize the variable for validity check
    validUser = False
    ' Loop through each row in the worksheet and compare with the entered credentials
    For i = 2 To lastRow ' Starting from row 2 (assuming row 1 is header)
        If ws.Cells(i, 1).Value = username And ws.Cells(i, 2).Value = password Then
            validUser = True
            Exit For ' Exit the loop if match found
        End If
    Next i
    ' If valid user, grant access; otherwise, show error
    If validUser Then
        MsgBox "Login successful! Access granted.", vbInformation
        ' Optionally, you can hide the login form and show the protected area
        Me.Hide
        Sheets("Sheet2").Visible = True ' Show protected sheet
    Else
        MsgBox "Invalid username or password.", vbCritical
    End If
End Sub

Explanation of the Code:

1. Variables:
   • ws is used to reference Sheet1, where the list of usernames and passwords is stored.
   • lastRow is used to determine how many rows are in the user list to loop through.
   • username and password hold the input values entered by the user in the form.
   • validUser is a flag that tells whether the login was successful or not.
2. Input Validation: The code first checks if the username and password fields are empty and shows a message if they are.
3. Looping Through User Data: It loops through all rows in Sheet1 to compare the input credentials with stored ones. If a match is found, the user is authenticated.
4. Granting Access: If the user is valid, a message is shown, and the form is hidden, along with the protection of the target sheet. The protected sheet (Sheet2) is made visible.
5. Error Handling: If no match is found, an error message is displayed.

5. Adding Security to the Protected Sheet

Once the user is authenticated, we can protect Sheet2 by hiding it initially and showing it after successful authentication.

You can protect a worksheet by setting its Visible property to xlSheetVeryHidden, and once authenticated, you can make it visible again with the following code:

Sheets(« Sheet2 »).Visible = xlSheetVeryHidden ‘ Hide sheet initially

6. Running the Authentication

To run the authentication system, simply open the workbook, then use the following code to show the login form when the workbook is opened:

1. Go to the ThisWorkbook module in the VBA editor.
2. Add the following code:

Private Sub Workbook_Open()
    ' Show the login form when the workbook is opened
    UserForm1.Show
End Sub

This will automatically show the login form when the workbook is opened, ensuring that the user is prompted to authenticate.

7. Enhancements and Considerations

1. Hashing Passwords: This simple system uses plain text passwords, but in a real-world scenario, you should hash passwords to protect them.
2. Adding Timeout: You could implement a timeout or a limit on the number of failed login attempts.
3. Logging Attempts: Consider keeping a log of failed login attempts for security reasons.
4. Multi-level Security: You could extend this system to handle different user roles (e.g., Admin, User) and show different levels of access based on the role.

Conclusion

This code provides a basic yet effective approach to implementing a user authentication system in Excel using VBA. By customizing this, you can add more sophisticated features like role-based access, encryption, and more secure handling of passwords.