How to create Login and Password when opening Workbook using Collections
Results 1 to 2 of 2

Thread: How to create Login and Password when opening Workbook using Collections
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2014
    Location
    Toronto
    Posts
    112
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to create Login and Password when opening Workbook using Collections

    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

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,975
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to create Login and Password when opening Workbook using Collections

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •