Login Form for Excel VBA Application with MS Access (ADODB)

A login form for an Excel VBA application gives your tool a professional front door: users type a user ID and password, and the form checks them against a user table before they get in. In this tutorial you build exactly the form from PK’s video: a UserForm designed in PowerPoint, connected to an MS Access database through an ADODB connection, with clear messages for a wrong ID or a wrong password. Before you start, one honest note: a VBA login form organises users and roles nicely, but it is not real security. You will see why, and what to use instead, further down.

Login form for Excel VBA application with User Login card, user ID and password boxes, Login and Clear buttons — The finished login form: a PowerPoint design used as a picture, with transparent text boxes on top.

Table of Contents

Watch the Video Tutorial

PK creates the Access user table, designs the form in PowerPoint, places it on a UserForm with transparent text boxes, then writes the ADODB login procedure and the Enter-key shortcut.

What You Will Build

An Access user table (TBL_UM) with ID, User_Id, User_Name, Password and Role.
A designer UserForm called frm_Login, built from a PowerPoint snapshot.
A User_Login procedure that opens an ADODB connection and checks the ID and password.
Validation messages for an empty box, an incorrect user ID and an incorrect password.
Enter-key login from either text box, plus a Clear button.

Part 1: Create the User Table in MS Access

Step 1: Create a blank database and a table

Open MS Access, create a Blank database and save it in the folder of your project. Go to Create → Table Design and add these fields:

ID: AutoNumber, set as the Primary Key.
User_Id, User_Name, Password and Role: Short Text.

Role separates an Admin from a normal User, and you can add columns such as an email ID. Save the table; in PK’s sample it is called TBL_UM.

MS Access table TBL_UM with ID, User_Id, User_Name, Password and Role fields for the VBA login form — The TBL_UM table with two users: Admin123 (role Admin) and User123 (role User).

Step 2: Mask the password column

Typed passwords are visible in the table, so PK masks them. Open the table in Design View, click the Password field, click the three dots next to Input Mask and choose Password in the Input Mask Wizard. Save, and the column now shows asterisks. Keep in mind that this only hides the display: the value is still stored as plain text.

Part 2: Set Up the Workbook and the ADODB Reference

Step 3: Save a macro-enabled workbook

Create a new workbook and save it as .xlsm (macro-enabled) or .xlsb (binary). PK uses .xlsb. Keep the database in a Database subfolder next to the workbook, because the code finds it from the workbook’s own path.

Step 4: Add the Microsoft ActiveX Data Objects reference

Press Alt+F11 to open the Visual Basic Editor, then go to Tools → References and tick Microsoft ActiveX Data Objects 2.8 Library. Without this reference VBA does not know what an ADODB.Connection is.

VBA References dialog with Microsoft ActiveX Data Objects 2.8 Library ticked for the ADODB connection — Tools → References → Microsoft ActiveX Data Objects 2.8 Library.

Part 3: Design the Login Form

Step 5: Insert the UserForm

Choose Insert → UserForm and make it a little bigger. In the Properties window set (Name) to frm_Login and Caption to Login Form.

Step 6: Draw the design in PowerPoint

VBA controls look plain, so PK draws the login card in PowerPoint: rounded rectangles for the boxes and buttons, a coloured header strip, user and lock icons and a footer line. Each element is a separate shape, so you can change any colour. When you are happy, set the slide zoom to 100%, capture the card with the Snipping Tool (take it slightly wider than the card) and save the image.

Step 7: Match the form background colour

The slide background in PK’s design is RGB 2, 86, 112 (check it in PowerPoint under Format Background → Color → More Colors). Give the UserForm the same colour in code:

Private Sub UserForm_Activate()

    Me.BackColor = RGB(2, 86, 112)

End Sub

Alternatively, open the form’s BackColor property, go to the Palette, right-click a white box to open Define Color, type the RGB values and click Add Color.

Step 8: Add the picture

Add an Image control, set BackStyle to Transparent and BorderStyle to None, then load your snapshot in the Picture property. The design is now on the form, but it is only a picture, so the boxes and buttons do nothing yet.

Step 9: Add labels for the heading and buttons

Add a Label for the User Login heading with a transparent BackStyle, a bigger font and centred text. Copy it onto each button for the Login and Clear captions, use a smaller font (about 9) and nudge them into place with the Top property.

Step 10: Add the two text boxes

Drag a TextBox over the first rounded box. Set the Height to about 20, BackStyle to Transparent, BorderStyle to None, SpecialEffect to Flat and the font to Bold. Run the form and adjust Top a few points until the text sits in the middle of the rounded box (PK moved it from 120 to 124). Copy it for the password box and align both to the left.

For the second box (TextBox2) type an asterisk in the PasswordChar property, so whatever the user types appears as stars.

Step 11: Name the buttons

The Login and Clear labels sitting on the button shapes act as the buttons. In the Properties window rename them btn_Login and btn_Clear, so their Click procedures read clearly in the code.

Part 4: The Login Form VBA Code

Step 12: Validate the boxes on the Login button

Double-click the Login button and check that neither box is empty before you touch the database:

Private Sub btn_Login_Click()



    If Me.TextBox1.Value = "" Then

        MsgBox "Please enter the User Id", vbCritical

        Exit Sub

    End If



    If Me.TextBox2.Value = "" Then

        MsgBox "Please enter the Password", vbCritical

        Exit Sub

    End If



    Call User_Login(Me.TextBox1.Value, Me.TextBox2.Value)



End Sub

Step 13: Write the User_Login procedure

Insert a standard module (PK calls it Login_Module) and add the procedure that talks to Access. This is the code from the original tutorial:

Option Explicit



Sub User_Login(User_Id As String, Password As String)



Dim cnn As New ADODB.Connection

Dim rst As New ADODB.Recordset



Dim qry As String



cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\Database\Database.accdb"



qry = "SELECT * FROM TBL_UM WHERE User_id = '" & User_Id & "'"

rst.Open qry, cnn, adOpenKeyset, adLockOptimistic



If rst.RecordCount = 0 Then

MsgBox "Incorrect User Id", vbCritical

ElseIf rst.Fields("Password").Value = Password Then

MsgBox "Login successfully", vbInformation

Else

MsgBox "Incorrect password", vbCritical

End If



rst.Close

cnn.Close



End Sub

How the Login Code Works

The connection string

cnn.Open connects to the Access file. The provider part, Microsoft.ACE.OLEDB.12.0, stays the same for .accdb files. Data Source is the full path of the database. Using ThisWorkbook.Path keeps the path dynamic: move the whole folder and the form still finds the database.

The query and the recordset

The query selects the row whose User_Id matches what was typed. rst.Open runs it on the connection with the adOpenKeyset cursor and the adLockOptimistic lock type. A keyset cursor gives a reliable RecordCount, which the next line needs. For a large database or many users, pick the cursor and lock type carefully; the ADODB connection chapter explains every option.

Three outcomes

RecordCount = 0: no user with that ID exists, so the message is Incorrect User Id.
Password field matches: the login succeeds. This is where your real application continues, for example by hiding the login form and showing the main form.
Otherwise: the ID exists but the password does not match, so the message is Incorrect password.

Finally the recordset and the connection are closed, so the database file is not left locked.

Step 14: Log in with the Enter key

Users expect Enter to work. Add a KeyDown event to each text box; key code 13 is Enter:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    If KeyCode = 13 Then btn_Login_Click

End Sub



Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    If KeyCode = 13 Then btn_Login_Click

End Sub

Step 15: The Clear button

The Clear button simply empties both boxes. The video does not show its lines, so here is a minimal version:

Private Sub btn_Clear_Click()

    Me.TextBox1.Value = ""

    Me.TextBox2.Value = ""

    Me.TextBox1.SetFocus

End Sub

Test it: a wrong ID shows Incorrect User Id, a right ID with a wrong password shows Incorrect password, and the correct pair shows Login successfully.

Is a VBA Login Form Secure? An Honest Answer

This section is an addition from general Excel and Access knowledge; it is not covered in the video.

No. A login form in Excel VBA is a convenience and role-management feature, not a security control. Here is why:

Macros can be bypassed. Anyone who opens the workbook with macros disabled never sees the form, and the data on the sheets is right there.
VBA project passwords and sheet or workbook protection are weak. They stop accidents, not a determined user; tools to remove them are easy to find.
The passwords are stored as plain text. The Access input mask hides them on screen only. Anyone who can open the .accdb file can read them.
The query is built by joining text. A user ID containing a single quote breaks the query, and crafted input could change it (SQL injection).

If you keep the form, harden it: never reuse real network passwords, put the database in a folder only authorised users can reach, and use a parameterised query instead of text joining. These lines replace the qry = ... and rst.Open qry, ... lines in User_Login; adVarWChar (value 202) and the other constants come from the ActiveX Data Objects reference you added in Step 4:

Dim cmd As New ADODB.Command

Set cmd.ActiveConnection = cnn

cmd.CommandText = "SELECT * FROM TBL_UM WHERE User_Id = ?"

cmd.Parameters.Append cmd.CreateParameter("uid", adVarWChar, adParamInput, 255, User_Id)

rst.CursorType = adOpenKeyset

rst.Open cmd

For data that really must be protected, rely on things outside VBA: Windows or SharePoint folder permissions, file encryption with a strong password (File → Info → Protect Workbook → Encrypt with Password), or a proper database server with its own user accounts.

Common Errors and Fixes

Additions from general VBA practice; not shown in the video.

Compile error: User-defined type not defined

The ActiveX Data Objects reference is missing. Add it under Tools → References (2.8 or the newest 6.x library both work).

Provider cannot be found or is not registered

The Access Database Engine (ACE) is not installed, or its 32/64-bit version does not match Office. Install the Microsoft Access Database Engine that matches your Office bitness.

Could not find file

The database is not where the connection string says. With ThisWorkbook.Path, the file must be in the Database subfolder and the workbook must be saved first.

Tips

Show the login form from Workbook_Open, and set the form’s ShowModal property to True so nothing else can be clicked behind it.
Store the logged-in user’s name and role in a public variable to enable or hide Admin features.
Microsoft documents the connection object in the ADO Connection object reference.

Want It Ready-Made?

If you would rather start from the finished file, the Login Form for Excel VBA based Application on NextGenTemplates.com includes the designed form, the Access database and the code from this tutorial. For complete VBA applications and utilities, browse the Excel VBA tools collection.

Frequently Asked Questions

How do I create a login form in Excel VBA?

Insert a UserForm with a user ID box, a password box with PasswordChar set to an asterisk and a Login button. The button calls a procedure that looks up the user in a table and compares the password, as shown in Part 4.

Can a VBA login form protect my Excel data?

Not on its own. Macros can be disabled and VBA or sheet passwords can be removed, so treat the form as user and role management. Protect sensitive data with folder permissions or file encryption.

Why connect the login form to MS Access instead of a worksheet?

Keeping users in a separate Access file means several workbooks can share one user list, and you can add or remove users without editing the Excel file.

Which reference do I need for the ADODB connection?

Microsoft ActiveX Data Objects, added in the Visual Basic Editor under Tools and References. The video uses version 2.8; newer 6.x versions also work.

How do I make the Enter key log the user in?

Add a KeyDown event to each text box and call the Login button procedure when the key code is 13, which is the Enter key.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

You now have a complete login form for an Excel VBA application: an Access user table, a PowerPoint-designed UserForm with transparent text boxes, a User_Login procedure that checks the ID and password through ADODB, and Enter-key support. Use it to give your tools a polished start screen and role-based features, and remember that real protection for sensitive data comes from permissions and encryption, not from VBA.