Creating multiple login usernames/passwords in workbook

[SonicBoomGolf]

I am sure that what I want to do can be done, but I don't have any idea where to start with this. On that note, this post is meant to be a brainstorming session

I have a tab of a workbook devoted to inputing information that is used to develop a profile on a particular person. Upon completing all the fields, the person hits my "Create Profile" button and their information is entered into my "database" (a different tab in the workbook). Pretty straight forward so far.

What I would like to do is have the person also enter a username and password on my profile creation tab so that when they enter the workbook next time they can enter their username and password and they will be directed to their specific tab in the workbook (for arguments sake we will make this their personal "profile" tab)

I guess that what I am looking for is a login prompt upon opening the file that asks for the users name and password. The login would check the "database" tab to see if the login matches the current username/password combinations on file. If the login matches, they are directed to their special tab. If they don't have a username/password then there will also be the option to create a profile.

There would be multiple profiles, each with a different login name/password chosen by the creator. None of them are accessing the file at the same time.

So what do the pros think??

 

[SonicBoomGolf]

HalfAce, we need you here!! It isn't an Excel party without you!!

 

[HalfAce]

No, no... You guys are too kind.
I don't at all feel taken over.
I'm just planning on learning something new and cool from whatever Nimrod comes up with this time!

Me before I party =

Me while I party =

Me after I party =

 

[SonicBoomGolf]

I keep trying to find out how to go to a specific sheet based on the login info, but I can't do it :x Any ideas?

 

[Nimrod]

Private Sub UserForm_Initialize()

    'SET PASSWORD TEXT BOX SO ONLY SHOWS STARS
    txtPassword.PasswordChar = "*"
    
    ' DISABLE OK COMMAND BUTTON
    cmdOK.Enabled = False
    
End Sub


Private Sub txtUserName_Change()
'ONLY ENABLE OK BUTTON IF BOTH TEXT BOXES HAVE VALUE
cmdOK.Enabled = Len(Trim(txtPassword)) > 0 And Len(Trim(txtUserName)) > 0
End Sub


Private Sub txtPassword_Change()
'ONLY ENABLE OK BUTTON IF BOTH TEXT BOXES HAVE VALUE
cmdOK.Enabled = Len(Trim(txtPassword)) > 0 And Len(Trim(txtUserName)) > 0
End Sub


Private Sub cmdOK_Click()
With Sheets("Sample Database")

'FIND ROW THAT USER HAS PROFILE ON BY FINDING HIS UNIQUE LOGIN NAME
ProfileRow = UsersProfileRow(Trim(txtUserName), .Name, "A")

'IF USERNAME PROFILE ROW NOT FOUND EXIT OK SUB
If ProfileRow = 0 Then Exit Sub

'IF PASSWORD DOES NOT MATCH THEN EXIT OK SUB
If Not ValidPassword(ProfileRow, .Name, "B", txtPassword) Then Exit Sub

'GET SHEET NAME AND SELECT USERS SHEET
UserHomeSheet = .Cells(ProfileRow, 28).Value
On Error Resume Next
Sheets(UserHomeSheet).Select

End With ' DATA SHEET

' EXIT LOGIN FORM
Me.Hide
Unload Me
End Sub



Public Function UsersProfileRow(Login, ShName, Col) As Integer
' FIND USERS PROFILE ROW IN DATA BASE BASED ON USER NAME

    With Worksheets(ShName).Columns(Col & ":" & Col)
        Set c = .Find(Login, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            UsersProfileRow = c.Row
        Else
            UsersProfileRow = 0
            pt = MsgBox("Invalid User Name", vbCritical, "Login Failed")
        End If
    End With
End Function


Public Function ValidPassword(Rw, ShName, Col, PassWd) As Boolean
' COMPARE ENTERED PASSWORD WITH ON STORE IN PROFILE SHEET
If Sheets(ShName).Range(Col & Rw).Value = PassWd Then
    ValidPassword = True
Else
    ValidPassword = False
    pt = MsgBox("Invalid Password", vbCritical, "Login Failed")
End If

End Function

ASSUMPTION:
- OK command button name is cmdOK
- Password text box name is txtPassword
- Login name text box named txtUserName
- Profile sheet name is Sample Database
- UserNames MUST BE UNIQUE
- UserNames in Col A of data sheet
- Passwords in Col B of data sheet

 

[Nimrod]

IS THERE ANYTHING YOU WOULD LIKE ME TO EXPLAIN ???

 

[SonicBoomGolf]

Funny guy!!!! I definetly could use a LITTLE explanation here. LOL. Thanks!!!! You are definetly getting a Christmas Card and a half gallon of egg nog from me this year.

 

[Nimrod]

Funny guy!!!! I definetly could use a LITTLE explanation here. LOL. Thanks!!!! You are definetly getting a Christmas Card and a half gallon of egg nog from me this year.

So I've commented each procedure with large print read each one and then let me know where you would like me to start :wink:

COUPLE OF CONCEPTS :

During the opening of the form I've done 2 things :
1- Set the Password text field so that "*" show up for each letter typed in
2- Disable the "OK" button ... the OK button will only be enabled when the user has put values in both the UserName Text box and the Password Text box

Notice the Change Events of Both Text Boxes:
In the change event I check to see if both text boxes have a string in them. If either text box is blank then the "OK" button is disabled. In other words no-ticky-no-washy. I think good user interfaces quide the user thru data entry process instead of waiting until the user has hit commit :wink:

Module Code:
I think that you can see that I try to make the "Main Procedure" do very little but direct the flow of the process. This makes for easy maintenance and upgrades.

What else would you like explained ???

CAUTIONS:
This process is only for administering the opening of sheets by checking "Current "usernames and passwords. You still need to work out a process for the creating new accounts and updating old passwords.
You will also need to make sure all user Login Names are unique !

SUGGESIONS:
1- The profile sheet should be at least "Very Hidden"
2- We could also "hide" the profile sheet outside of the workbook which would (a) mean the book would be non-functional if taken away (b) mean copies of passwords would not be passed with any copies of the workbook.

 

[SonicBoomGolf]

I am going to look over this some more tomorrow. Too tired right now to look at this more. Thanks for the help and I will respond tomorrow.

 

[SonicBoomGolf]

I have tried inserting the code you gave me into my userform, but I must have placed the Public/Private Subs in the wrong location or something similar. For example, when I enter text into the userform for logging in I can still see the numbers/letters being entered. This tells me that I obviously mis-entered or mis-placed the code.

I completely understand everything you are doing in your code, but I think I am having trouble placing it correctly throughout my userform so that it flows correctly.

To break it down for you where I put everything:

IN THIS WORKBOOKPrivate Sub UserForm_Initialize()

'SET PASSWORD TEXT BOX SO ONLY SHOWS STARS
txtPassword.PasswordChar = "*"

' DISABLE OK COMMAND BUTTON
cmdOK.Enabled = False

End Sub


TEXT BOX 1
Private Sub txtUserName_Change()
'ONLY ENABLE OK BUTTON IF BOTH TEXT BOXES HAVE VALUE
cmdOK.Enabled = Len(Trim(txtPassword)) > 0 And Len(Trim(txtUserName)) > 0
End Sub

TEXT BOX 2Private Sub txtPassword_Change()
'ONLY ENABLE OK BUTTON IF BOTH TEXT BOXES HAVE VALUE
cmdOK.Enabled = Len(Trim(txtPassword)) > 0 And Len(Trim(txtUserName)) > 0
End Sub

"OK" USERFORM BUTTON
Private Sub cmdOK_Click()
With Sheets("Sample Database")

'FIND ROW THAT USER HAS PROFILE ON BY FINDING HIS UNIQUE LOGIN NAME
ProfileRow = UsersProfileRow(Trim(txtUserName), .Name, "A")

'IF USERNAME PROFILE ROW NOT FOUND EXIT OK SUB
If ProfileRow = 0 Then Exit Sub

'IF PASSWORD DOES NOT MATCH THEN EXIT OK SUB
If Not ValidPassword(ProfileRow, .Name, "B", txtPassword) Then Exit Sub

'GET SHEET NAME AND SELECT USERS SHEET
UserHomeSheet = .Cells(ProfileRow, 28).Value
On Error Resume Next
Sheets(UserHomeSheet).Select

End With ' DATA SHEET

' EXIT LOGIN FORM
Me.Hide
Unload Me
End Sub

PUBLIC FUNCTIONS are placed in "This Workbook".

Even though you explained everything beautifully, I think I still managed to jack it up somehow. Have I placed everything in the correct position from what I describe??

 

[Nimrod]

Hello trackman69
... No ... No ... and No .. :wink:

If you have gone into the VBA editor and created a userform, see earlier post for instructions, then just :
1. Go into VBA editor and locate userform
2. Make sure the TextBoxes and Command buttton's have been named as I indicated in a previous post
3. Double click on the user form to open it's own code module
4. COPY/PASTE ALL the code I gave you into the ONE userform module

NOTE: To see and rename an object on your VBA form
1. click on the object eg (textbox)
2. find the objects seperate Property window , usually in the lower left hand corner of VBA window
3.Find the Property called "Name" and changes it's value to txtPassword and txtUserName... do the same with the command button as well