How to create Login and Password when opening Workbook using Collections

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
170
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top