Lecture Notes: Creating a Login Page in Excel

Introduction

• Presenter: Dilip from Data Laps
• Topic: Creating a login page for a spreadsheet or Excel-based automated application.
• Purpose: To add a restriction so that only authorized users can access the application.

Overview

• Utilize an existing data entry form to integrate a login feature.
• Users must enter credentials to access the data entry application.
• Wrong credentials terminate the application.
• Link to a tutorial is provided for creating the data entry form.

Steps to Create a Login Form

Accessing Visual Basic for Applications

1. Click on the Developer tab.
2. Under the Code group, click on Visual Basic.
3. In the Microsoft Visual Basic for Applications window, add a blank form by clicking Insert > User Form.

Designing the Login Form

1. Form Properties:
   • Change the form name to frmLogin.
   • Set background color to white.
   • Set caption to "Login to Data Entry Application".
   • Set height to 198 and width to 385.
2. Add a Frame:
   • Insert a frame from the toolbox.
   • Change frame border color to blue highlight.
   • Set border style to single.
   • Caption: "Login Credentials".
   • Font size: 10.
3. Insert Image for User Icon:
   • Use image control to insert a user icon.
   • Set picture size mode to stretch.
   • No border color.
4. Text Boxes for User ID and Password:
   • Add two text boxes for user ID and password.
   • Insert icons for user ID and password.
   • Set properties accordingly.
5. Setting Password as Stars:
   • In the password text box, set PasswordChar to "*".
6. Command Buttons:
   • Add "Login" and "Clear" buttons.
   • Set properties: name, accelerator key, back color, and caption.

Coding the Login Functionality

Initialize Variables and Controls

• Declare global variable Public loginInstance As Integer to count invalid attempts.
• Initialize controls on form load to clear values and set focus.

Handle Login Button Click

1. Declare variables for user and password.
2. Assign values from text boxes.
3. Conditional check for correct credentials:
   • If condition: Check against admin/admin or user/user.
   • Else condition: Handle invalid attempts.
     • If attempts < 3, prompt retry.
     • If attempts >= 3, show exceeded attempts message and terminate.

Clear Button Functionality

• Clear text boxes and reset focus.

Disable Form Close Button

• Handle QueryClose event to cancel if close initiated by user.

Workbook Open Event

• Initialize global variable.
• Hide Excel application until correct login credentials are entered.

Testing

• Save and compile VBA code.
• Test the application by opening the Excel file.
• Ensure macros are enabled to fire the login process.

Conclusion

• This feature allows restricting access in Excel applications.
• Can be integrated into any spreadsheet or application.

Final Remarks

• Encourage to like, share, subscribe, and comment.
• Invites users to add the feature to their applications.
• End of tutorial.