How to create Login and Password when opening Workbook using Collections


Board Regular
Jan 23, 2014
I am trying to make a login prompt when opening a workbook.

In this workbook I have a Sheet labeled, "Userbase".

In the "Userbase" sheet there are 4 columns.
In cell "A2", the text "First Name" is set.
In cell "B2", the text "Last Name" is set.
In cell "C2", the text "Logon ID" is set.
In cell "D2", the text "Password" is set.

I have created a UserForm with 2 labels; "Username", and "Password", 2 text boxes ( 1 for Username, 1 for Password).

I have started the code in ThisWorkbook as follows:

Private Sub Workbook_Open()
Dim wb As Workbook
Dim ws As Worksheet
Dim userbase As Collection
Dim Rng As Range
Dim Cell As Range
Dim sh As Worksheet
Dim vNum As Variant

Set wb = ActiveWorkbook
Set ws = wb.Sheets("Userbase")
Set Rng = ws.Range("C3", ws.Range("C3").End(xlDown))
Set userbase = New Collection

On Error Resume Next
For Each Cell In Rng.Cells
userbase.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0

if txt.bxUsername.txt

End Sub

I have searched for how to use "Collections" but am not sure. From what I understand, the above code loads the usernames into a collection. What I want it to do is have the "Key" in the Collection for each Username to be paired. Then when a user enters their logon credentials (Username and Password), it will cross reference the input values in the UserForm with the data in the Collection. I can't figure out how to link the "key" (Password) to the Username in the collection and how to test for membership. Can someone help me please?


Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.


Well-known Member
Jul 3, 2012
Office Version
have a look here & see if of any help:RE: Login Userform

If trying to validate authorised users you could consider checking their network username agianst a table of authorised users.

Validuser = Environ("USERNAME")

returns user name. You can then use Application.Match or Find to search your table.

Hope helpful


Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...