How to create Login and Password when opening Workbook using Collections

[pingme89]

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?

Thanks

 

[dmt32]

Hi,
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

Dave