Checking Cell in Range

[shades]

I have set up a login system that does everything I need. I have an InputBox that asks for name, then another InputBox that ask for login ID. If that is done, the login page is hidden, and the presentation page is made visible. Then the login info is used to set up a Validation cell that allows only selected choices to show up. Each user has his/her own choices. Everything works great if a person enters a correct name...

However, if the person enters a wrong name (i.e. one not on the password list), then I get an error (on bold line). I would like to set up the beginning of the code such that it compares the first InputBox (Name) (which is entered into cell H4) against the list of names (in this case, A2:A6). If it is in there, then continue with the code. If not, present a MsgBox that says "Your name is not listed". Here is the code:

Sub PassWordTest()
    Dim LastName As String, PassWord As String
    Sheets("Passwords").Range("H4").Value = InputBox("Enter Last Name")
If Sheets("Passwords").Range("H5").Value = Sheets("Passwords").Range("H4").Value Then
    Sheets("Passwords").Range("I4").Value = InputBox("Enter Password")
    If Sheets("Passwords").Range("I5").Value = Sheets("Passwords").Range("I4").Value Then
        Sheets("ShowData").Range("B2").Value = Sheets("Passwords").Range("H4").Value
        ActiveWorkbook.Sheets("ShowData").Visible = True
        ActiveWorkbook.Sheets("Intro").Visible = False
        ActiveWorkbook.Sheets("ShowData").Select
    Else: MsgBox ("You have entered wrong Password")
    End If
     Else
    MsgBox ("Your Name is not listed")
    End If
End Sub

Again, if the name is in the list, this code works beautifully.

Any help is much appreciated.

[CBrine]

Give this a try.

Sub PassWordTest() 
Dim LastName As String, PassWord As String, UserName as string, Dim R as range,Found as boolean
GetUserName:
Username = InputBox("Enter Last Name") 
For each R in range("A2",range("A65000").end(xlup))
    If r.value = UserName then Found = True
Next R
If Not(Found = True) then
    Msgbox "User Name not Found"
    Goto GetUserName
End If
Sheets("Passwords").Range("H4").Value 
Sheets("Passwords").Range("I4").Value = InputBox("Enter Password") 
If Sheets("Passwords").Range("I5").Value = Sheets("Passwords").Range("I4").Value Then 
    Sheets("ShowData").Range("B2").Value = Sheets("Passwords").Range("H4").Value 
    ActiveWorkbook.Sheets("ShowData").Visible = True
    ActiveWorkbook.Sheets("Intro").Visible = False 
    ActiveWorkbook.Sheets("ShowData").Select 
Else: MsgBox ("You have entered wrong Password") 
End If
End Sub

Edit: I noticed some of your code was trying to deal with the multiple users, I had to remove that.
Currently you are looking at a static password for all users? Is that what you want? It could be modified to have user specfic password. Let me know.

[shades]

Thanks. There will be multiple users (20+), each with a unique password.

[CBrine]

After looking closely at your code, I decided to do a rework. I set it up to first see if the userName exists, and if it does then get the password. Then it checks to see if the inputbox password matches the real password. It will continue to put up the inputboxes until a correct response is entered.

Sub PassWordTest()
Dim LastName As String, Password As String, UserName As String, R As Range, Found As Boolean, Password As String,PasswordCorrect as boolean
'Cycle through all cells in column A until a match is found with the inputputbox
Dou Found = True
UserName = InputBox("Enter Last Name")
Workbooks("Password").Activate
For Each R In Range("A2", Range("A65000").End(xlUp))
    If R.Value = UserName Then
        Found = True
        Password = R.Offset(0, 1)
Next R
Loop
Dou PasswordCorrect = true
'Check to see if user password is correct.  Password must be in column to the left of username on Password worksheet
If Password = InputBox("Please enter Password") Then
    Sheets("ShowData").Range("B2").Value = Sheets("Passwords").Range
    ("H4").Value
    PasswordCorrect =True
    ActiveWorkbook.Sheets("ShowData").Visible = True
    ActiveWorkbook.Sheets("Intro").Visible = False
    ActiveWorkbook.Sheets("ShowData").Select
Else
    MsgBox ("You have entered wrong Password")
End If
Loop
End Sub

[CBrine]

Sorry, 1 last post. I have tested this code and eliminated all the syntax errors. It seems to work. Give it a try. Sorry about the other mistakes.

Sub PassWordTest()
Dim LastName As String, Password As String, UserName As String, R As Range, Found As Boolean, PasswordCorrect As Boolean
'Cycle through all cells in column A until a match is found with the inputputbox
Do Until Found = True
    UserName = InputBox("Enter Last Name")
    Worksheets("Password").Activate
    For Each R In Range("A2", Range("A65000").End(xlUp))
        If R.Value = UserName Then
            Found = True
            Password = R.Offset(0, 1)
        End If
    Next
    If Found = False Then MsgBox "User does not exist"
Loop
Do Until PasswordCorrect = True
    'Check to see if user password is correct. Password must be in column to the left of username on Password worksheet
    If Password = InputBox("Please enter Password") Then
        Sheets("ShowData").Range("B2").Value = Sheets("Passwords").Range("H4").Value
        PasswordCorrect = True
        ActiveWorkbook.Sheets("ShowData").Visible = True
        ActiveWorkbook.Sheets("Intro").Visible = False
        ActiveWorkbook.Sheets("ShowData").Select
    Else
        MsgBox ("You have entered wrong Password")
    End If
Loop
End Sub

PS-Just remembered I can attach a file. So here is the test sheet I used. PS-If you have just 1 user in the list, this will not work. You will need to put some extra code for only 1 person.

[Tommy]

I took a different approach. I used vlookup, the drawback to this is the list of name should be in aphabetical order and when new users are added they will need to be added in order. I did not look at the passwords, it could be done the same way, but as far as I can tell at this time the password would have to exist and there would be no check for user name = correct password, which means the user could enter any valid password.

Sub PassWordTest()
    Dim LastName As String, PassWord As String
    Sheets("Passwords").Range("H4").Value = InputBox("Enter Last Name")
    'MsgBox Application.WorksheetFunction.VLookup(Sheets("Passwords").Range("H4").Value, Range("A2:A6"), 1, True)
    If Application.WorksheetFunction.VLookup(Sheets("Passwords").Range("H4").Value, Range("A2:A6"), 1, True) = Sheets("Passwords").Range("H4").Value Then
    Sheets("Passwords").Range("I4").Value = InputBox("Enter Password")
    If Sheets("Passwords").Range("I5").Value = Sheets("Passwords").Range("I4").Value Then
        Sheets("ShowData").Range("B2").Value = Sheets("Passwords").Range("H4").Value
        ActiveWorkbook.Sheets("ShowData").Visible = True
        ActiveWorkbook.Sheets("Intro").Visible = False
        ActiveWorkbook.Sheets("ShowData").Select
    Else: MsgBox ("You have entered wrong Password")
    End If
    Else
    MsgBox ("Your Name is not listed")
    End If
End Sub

[shades]

I took a different approach. I used vlookup, the drawback to this is the list of name should be in aphabetical order and when new users are added they will need to be added in order. I did not look at the passwords, it could be done the same way, but as far as I can tell at this time the password would have to exist and there would be no check for user name = correct password, which means the user could enter any valid password.

Sub PassWordTest()
Dim LastName As String, PassWord As String
Sheets("Passwords").Range("H4").Value = InputBox("Enter Last Name")
'MsgBox Application.WorksheetFunction.VLookup(Sheets("Passwords").Range("H4").Value, Range("A2:A6"), 1, True)
If Application.WorksheetFunction.VLookup(Sheets("Passwords").Range("H4").Value, Range("A2:A6"), 1, True) = Sheets("Passwords").Range("H4").Value Then
    Sheets("Passwords").Range("I4").Value = InputBox("Enter Password")
    If Sheets("Passwords").Range("I5").Value = Sheets("Passwords").Range("I4").Value Then
        Sheets("ShowData").Range("B2").Value = Sheets("Passwords").Range("H4").Value
        ActiveWorkbook.Sheets("ShowData").Visible = True
        ActiveWorkbook.Sheets("Intro").Visible = False
        ActiveWorkbook.Sheets("ShowData").Select
    Else: MsgBox ("You have entered wrong Password")
    End If
Else
    MsgBox ("Your Name is not listed")
End If
End Sub

Tommy, this works in line with what I was trying to do. The password connection is made by mini test area using inputs and lookups:

H3 = "Last Name"
I3 = "Password"
 
H4 = Name from InputBox
H4 = Password from InputBox
 
H5 = =INDEX(PWList,MATCH($H$4,LName,),MATCH($H$3,$A$1:$B$1,))
I5 = =INDEX(PWList,MATCH($H$4,LName,),MATCH($I$3,$A$1:$B$1,))

Where PWList is a dynamic range for columns A and B, and LName is dynamic range for col. A.

That's why I needed the InputBox strings put into the specific cells on the Password worksheet. And I needed the Name from the InputBox put into the ShowData worksheet, which then becomes the trigger for the validation cell to be populated.

So, Tommy, your solution worked very well with what I was trying to do. Thank you.
------------------------

Cbrine,

I tried your code, and it worked, except I had trouble getting the UserName put into the ShowData worksheet, cell B2, which would be the trigger for the validation code. The UserName couldn't be entered into the cell until after the password Do Loop (if I understand it correctly), but then the UserName seemed to have lost its content.

[Tommy]

Glad I could help

[CBrine]

Shades,
Didn't realize you will validating off index(match()), and for me I wouldn't have done it that way (As you can see from my code, couldn't figure out why you were storing the values on a single cell and expecting a list match.) I was doing the entire validation in the code, that's why I had reworked it so much. Oh well, as long Tommy got you there.
If you are interested in the code, let me know what sheetname and address you want to store UserName and Password. It should intergrate into your code fine after that.

[shades]

Shades,
Didn't realize you will validating off index(match()), and for me I wouldn't have done it that way (As you can see from my code, couldn't figure out why you were storing the values on a single cell and expecting a list match.) I was doing the entire validation in the code, that's why I had reworked it so much. Oh well, as long Tommy got you there.
If you are interested in the code, let me know what sheetname and address you want to store UserName and Password. It should intergrate into your code fine after that.

Sorry, I realized I didn't give enough background on that part of the setup. I am still interested in your approach.

On the Passwords worksheet, I would put the following values.

H3 = "UserName" (InputBox)
I3 = "Password" (InputBox)

And on the ShowData worksheet, I would put

B2 = "UserName" (InputBox)

[CBrine]

Shades,
I didn't encounter the UserName reference issue you had mentioned? The code will now store username and password, that is validated via the code, in the H4 and I4 cells in Password. It will also store the validated UserName on the Showdata Worksheet in B3. I'm also making the assumption that your passwords are stored in B2:B6 of the Password worksheet.

Sub PassWordTest()
Dim LastName As String, Password As String, UserName As String, R As Range, Found As Boolean, PasswordCorrect As Boolean
Found = False
PasswordCorrect = False
'Cycle through all cells in column A until a match is found with the inputputbox
Do Until Found = True
    UserName = InputBox("Enter Last Name")
    Worksheets("Password").Activate
    For Each R In Range("A1", Range("A65000").End(xlUp))
        If R.Value = UserName Then
            Found = True
            Password = R.Offset(0, 1)
        End If
    Next
    If Found = False Then MsgBox "User does not exist"
Loop
Do Until PasswordCorrect = True
    'Check to see if user password is correct. Password must be in column to the left of username on Password worksheet
    If Password = InputBox("Please enter Password") Then
        Range("H4").Value = UserName
        Range("I4").Value = Password
        Sheets("ShowData").Range("B3").Value = UserName
        PasswordCorrect = True
        ActiveWorkbook.Sheets("ShowData").Visible = True
        ActiveWorkbook.Sheets("Intro").Visible = False
        ActiveWorkbook.Sheets("ShowData").Select
    Else
        MsgBox ("You have entered wrong Password")
    End If
Loop
End Sub

[shades]

Okay, here is the final code, modified from Tommy's suggestion above. One concern I had was activating the worksheet with the passwords while someone logged in. And I found that the sheet with the passwords had to be available (not hidden, and not protected). However, ScreenUpdating = False solved that problem. I also hid the password worksheet, now renamed "Admin" (less revealing ).

Also had to add lines to rehide the Admin worksheet, in both situations: wrong name, or wrong password. This seems to meet all my requirements.

Thanks, Tommy and CBrine.

Sub UserPassword()
Dim LastName As String, PassWord As String
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Admin").Visible = True
ActiveWorkbook.Sheets("Admin").Activate
Sheets("Admin").Range("H4").Value = InputBox("Enter Last Name")
'MsgBox Application.WorksheetFunction.VLookup(Sheets("Passwords").Range("H4").Value, Range("A2:A6"), 1, True)
If Application.WorksheetFunction.VLookup(Sheets("Admin").Range("H4").Value, Range("A2:A6"), 1, True) = Sheets("Admin").Range("H4").Value Then
    Sheets("Admin").Range("I4").Value = InputBox("Enter Password")
    If Sheets("Admin").Range("I5").Value = Sheets("Admin").Range("I4").Value Then
        Sheets("ShowData").Range("B2").Value = Sheets("Admin").Range("H4").Value
        ActiveWorkbook.Sheets("ShowData").Visible = True
        ActiveWorkbook.Sheets("Intro").Visible = False
        ActiveWorkbook.Sheets("Admin").Visible = False
        ActiveWorkbook.Sheets("ShowData").Select
    Else:
        ActiveWorkbook.Sheets("Admin").Visible = False
        MsgBox ("You have entered wrong Password")
    End If
Else
ActiveWorkbook.Sheets("Admin").Visible = False
MsgBox ("Your Name is not listed")
End If
Application.ScreenUpdating = True
End Sub

[shades]

Shades,
I didn't encounter the UserName reference issue you had mentioned? The code will now store username and password, that is validated via the code, in the H4 and I4 cells in Password. It will also store the validated UserName on the Showdata Worksheet in B3. I'm also making the assumption that your passwords are stored in B2:B6 of the Password worksheet.

Yes, the assumption about the password being in B2:B6 is correct.

However, when I tried this code, it gets stuck in an endless loop, never recognizing the UserName. Not sure what I am doing wrong.

Sub PassWordTest()
Dim LastName As String, Password As String, UserName As String, R As Range, Found As Boolean, PasswordCorrect As Boolean
Found = False
PasswordCorrect = False
'Cycle through all cells in column A until a match is found with the inputputbox
Do Until Found = True
UserName = InputBox("Enter Last Name")
Worksheets("Password").Activate
For Each R In Range("A1", Range("A65000").End(xlUp))
    If R.Value = UserName Then
        Found = True
        Password = R.Offset(0, 1)
    End If
Next
If Found = False Then MsgBox "User does not exist"
Loop
Do Until PasswordCorrect = True
    'Check to see if user password is correct. Password must be in column to the left of username on Password worksheet
    If Password = InputBox("Please enter Password") Then
        Range("H4").Value = UserName
        Range("I4").Value = Password
        Sheets("ShowData").Range("B3").Value = UserName
        PasswordCorrect = True
        ActiveWorkbook.Sheets("ShowData").Visible = True
        ActiveWorkbook.Sheets("Intro").Visible = False
        ActiveWorkbook.Sheets("ShowData").Select
    Else
        MsgBox ("You have entered wrong Password")
    End If
Loop
End Sub

[CBrine]

Take a look at the attached worksheet. I've implemented the entire set of code, it should give an idea where the problem is. Just press the button on the password worksheet.

PS: UserName and Password are case sensitive.

[shades]

Take a look at the attached worksheet. I've implemented the entire set of code, it should give an idea where the problem is. Just press the button on the password worksheet.

PS: UserName and Password are case sensitive.

After some fine tuning, I finally got it working on my setup. Did I mention that I am slow? Anyway, thank you. I know other projects in which this will be handy. I like the use of Offset for the password.

Now, another question: What are the advantages/disadvantages of each approach?

One possible issue I find with this last approach (CBrine's), is that there does not seem to be an easy way out of the loop - for the user. That is, what of the person gets frustrated, can't remember password and just wants to get out. Right now, it will continue cycling through passwords. In other words, I would like the Cancel button, to actually exit the entire macro.

[shades]

Modified again. This I used Tommy's code and added a Cancel action, so someone can get out of the entry sequence.

Sub UserPassword()
Dim LastName As String, PassWord As String, myName As String
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Admin").Visible = True
ActiveWorkbook.Sheets("Admin").Activate
myName = InputBox("Enter Last Name")
If myName = "" Then
    ActiveWorkbook.Sheets("Admin").Visible = False
    Exit Sub
Else
End If
Sheets("Admin").Range("H4").Value = myName
'MsgBox Application.WorksheetFunction.VLookup(Sheets("Passwords").Range("H4").Value, Range("A2:A6"), 1, True)
If Application.WorksheetFunction.VLookup(Sheets("Admin").Range("H4").Value, Range("A2:A6"), 1, True) = Sheets("Admin").Range("H4").Value Then
    Sheets("Admin").Range("I4").Value = InputBox("Enter Password")
    If Sheets("Admin").Range("I5").Value = Sheets("Admin").Range("I4").Value Then
        Sheets("ShowData").Range("B2").Value = Sheets("Admin").Range("H4").Value
        ActiveWorkbook.Sheets("ShowData").Visible = True
        ActiveWorkbook.Sheets("Intro").Visible = False
        ActiveWorkbook.Sheets("Admin").Visible = False
        ActiveWorkbook.Sheets("ShowData").Select
    Else:
        ActiveWorkbook.Sheets("Admin").Visible = False
        MsgBox ("You have entered wrong Password")
    End If
Else
ActiveWorkbook.Sheets("Admin").Visible = False
MsgBox ("Your Name is not listed")
End If
Application.ScreenUpdating = True
End Sub

Okay, I'm done with it for a while!

[Zack Barresse]

... I would like the Cancel button, to actually exit the entire macro.

If MsgBox("You have entered the wrong password.", vbOKCancel) = vbCancel Then Exit Sub

That should take care of that, just replace the above line with your old one.

[Tommy]

Man ya take a nap and ya miss all the fun, bummer.

[CBrine]

Shades,
I assumed that it was for protection. If that was the case, you really don't want them to break out of the loop. Zack's suggestion should work fine, if you want them to be able to get out. I think you might recieve the message when they cancel the input box though? I would monitor for the cancel flag from the input box instead, like this.

Sub PassWordTest()
Dim LastName As String, Password As String, UserName As String, R As Range, Found As Boolean, PasswordCorrect As Boolean, p2 As String
Found = False
PasswordCorrect = False
'Cycle through all cells in column A until a match is found with the inputputbox
Do Until Found = True
    UserName = InputBox("Enter Last Name")
    If Len(UserName) = 0 Then Exit Sub
    Worksheets("Password").Activate
    For Each R In Range("A2", Range("A65000").End(xlUp))
        If R.Value = UserName Then
            Found = True
            Password = R.Offset(0, 1)
        End If
    Next
    If Found = False Then MsgBox "User does not exist"
Loop
Do Until PasswordCorrect = True
    p2 = InputBox("Please enter Password")
    If Len(p2) = 0 Then Exit Sub
    'Check to see if user password is correct. Password must be in column to the left of username on Password worksheet
    If Password = p2 Then
        Range("H4").Value = UserName
        Range("I4").Value = Password
        Sheets("ShowData").Range("B3").Value = UserName
        PasswordCorrect = True
        ActiveWorkbook.Sheets("ShowData").Visible = True
        ActiveWorkbook.Sheets("Intro").Visible = False
        ActiveWorkbook.Sheets("ShowData").Select
    Else
        MsgBox ("You have entered wrong Password")
    End If
Loop
End Sub

[NewGuy321]

I am doing an AS level project and was wondering if someone could help me to acieve the following.

"Customisation might include a password function with a dialog box, which presents a drop down list of users to choose from, asnd asks for the password and if this amtches the password entered, the dialog sheet is hiddent, the front page selected and permited access is acknowledged using a message box, otherwise access is denied. The lookup table contains the ezpected users with their given password, as well as Read/Write access password"

I have taken this directly out of the guidelines. I would like to have log on interface liek this for my solution in Microsoft Excel. I was wondering if anyone could help. I am a complete beginner with VB in excel so go slowly please. Thanx in advnace for the help