Consulting

Results 1 to 7 of 7

Thread: Userform To Act As A Login With A Password To Use The Spreadsheet

  1. #1

    Userform To Act As A Login With A Password To Use The Spreadsheet

    Hi,

    I have come up with the idea to create a userform to act as a login page for my project.

    I have the project complete and need a way to protect it.

    I want the userform to display an input box asking the user to enter their 'login ID'. This has to be either "SUPERVISOR" or "STAFF". Or If I have a table of 'users' and 'passwords' in sheet (USERS) to match them against that if it is easier?

    Upon correct entry i need it to then display another input box asking for a 'password'. This needs to be "SHOES" for both logins.

    If the login is not equal to SUPERVISOR or STAFF I need a message box displaying "username incorrect". and for it then to show the input box requesting the 'login ID' again.

    Again if the password is not equal to "SHOES" to display a message box displaying "password incorrect" Then show the input box requesting the password.

    Upon correct entry for both the username and password i would like my other userform to show. This is displayed by:

    Load UserForm1
    UserForm1.Show

    I would like the correct 'username' to be displayed in cell "D27" on the active sheet and 'password' to be displayed in cell "F27" also on the active sheet To show who is 'Logged On'.

    If there is a way to display the password using asterix's for security that would be useful.

    Sorry the post is long and I know it may take some time but it would be greatly appreciated if anyone can help.

    Thanks in advance,

    Danny

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Quote Originally Posted by dannyscroggi
    I have come up with the idea to create a userform to act as a login page for my project.

    I have the project complete and need a way to protect it.

    I want the userform to display an input box asking the user to enter their 'login ID'. This has to be either "SUPERVISOR" or "STAFF". Or If I have a table of 'users' and 'passwords' in sheet (USERS) to match them against that if it is easier?
    You could use a combox control and set the RowSource property of that control to point at the range of the sheet contatining the names. Better still, use a dynamic range name, then you can add to it without having to change the code. To do this, on the worksheet assuming the names are in column A, select A1, hit Ctrl-3, enter a name of NameList, enter a RefersTo value of
    =OFFSET($A$1,,,COUNTA($A:$A)<1)
    and OK out. Then enter NameList oin the RowSource property.

    You can make ths heet hidden to stop people seeing the data

    Quote Originally Posted by dannyscroggi
    Upon correct entry i need it to then display another input box asking for a 'password'. This needs to be "SHOES" for both logins.

    [IMG]file:///C:/DOCUME%7E1/Bob/LOCALS%7E1/Temp/moz-screenshot.jpg[/IMG] If the login is not equal to SUPERVISOR or STAFF I need a message box displaying "username incorrect". and for it then to show the input box requesting the 'login ID' again.

    Again if the password is not equal to "SHOES" to display a message box displaying "password incorrect" Then show the input box requesting the password.

    Upon correct entry for both the username and password i would like my other userform to show. This is displayed by:

    Load UserForm1
    UserForm1.Show
    This would be a simple textbox. Add a commandbutton to be pressed when both entries are complete with validation code like this

    [vba]

    Private Sub cmdOK_Click()
    If ComboBox1.Value <> "" And TextBox1.Text = "SHOES" Then
    Me.Hide
    UserForm1.Show
    End If
    End Sub
    [/vba]
    There is no need to check a valid name, because by using a combobox they cannot select an invalid name.


    Quote Originally Posted by dannyscroggi
    I would like the correct 'username' to be displayed in cell "D27" on the active sheet and 'password' to be displayed in cell "F27" also on the active sheet To show who is 'Logged On'.
    Just extend the OK button code with

    [vba]

    Private Sub cmdOK_Click()
    If ComboBox1.Value <> "" And TextBox1.Text = "SHOES" Then
    Me.Hide
    ActiveSheet.Range("D27").Value = ComboBox1.Value
    UserForm1.Show
    End If
    End Sub
    [/vba]
    note that I have showed the password, that is a bad idea, no point in having one if you print it!


    Quote Originally Posted by dannyscroggi
    If there is a way to display the password using asterix's for security that would be useful.
    A textbox has a PasswordChar property. You can set this to any value, asterisk is the obvious one, and that character is echoed as the user types in a value.

  3. #3
    Thank you very much XLD that worked!

    I have come up with the idea though that instead of having 2 users with the same password, to then have a table in a sheet named "Users" displaying 4 usernames each with their own password.

    I would then need 2 text boxes to enter the 'username' and then the 'password'.

    Then clicking the 'ok' button i would need it to lookup the username and password from my 'users' sheet and the 'users table'.

    I would then like for the user to only be able to try 3 times to '"log in" after the 3 incorrect tries I would like the workbook to close. This may need a +1 counter for incorrect entry, also displaying the appropriate messagebox informing the user of incorrect entry of either username or password.

    Any ideas anyone?

    Thanks.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    I would keep the combobox for the name as we described above. You cab set up a similar dynamic range name for passwords, say PwdList, and check it lik so

    [vba]

    If IsError(Application.Match(.Text, Worksheets("Users").Range("PwdList"), 0)) Then
    MsgBox "Invalid password"
    With Me.TextBox1
    .SelStart = 0
    .SelLength (Len(.Text))
    .SetFocus
    End With
    End If

    [/vba]

  5. #5
    xld I have tried the code you posted.

    I currently have this for my 'ok' button

    (sorry I dont know how to post code properly)

    [VBA]
    Private Sub CommandButton1_Click()

    If ComboBox1.value <> "" And TextBox1.Text = "SHOES" Then
    Me.Hide
    ActiveSheet.Range("D27").value = ComboBox1.value
    UserForm1.Show
    End If

    If IsError(Application.Match(ComboBox1.value, Worksheets("Users").Range("B2,B5"), 0)) Then
    MsgBox "Invalid password"
    With Me.TextBox1
    .SelStart = 0
    .SelLength (Len(.Text))
    .SetFocus
    End With
    End If

    End Sub
    [/VBA]


    I get an error at '.SelLength'

    Any fixes?
    Edited 17-Apr-07 by geekgirlau. Reason: insert vba tags

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Sorry, my error. I don't think you need that first test if you are checking against a sheet

    [vba]

    Private Sub CommandButton1_Click()

    If IsError(Application.Match(ComboBox1.Value, Worksheets("Users").Range("B2,B5"), 0)) Then
    MsgBox "Invalid password"
    With Me.TextBox1
    .SelStart = 0
    .SelLength = Len(.Text)
    .SetFocus
    End With
    End If

    End Sub
    [/vba]

  7. #7
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    Hi Danny,

    Welcome to the Board

    When you are posting code, the last step is to highlight the code text, then click on the "VBA" button. I've taken the liberty of editing your post to do this, so just keep it in mind for next time.

Posting Permissions

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