VBA to find a value in a list

[Guest]

My workbook prompts the user for his/her password via a macro using InputBox.

password = InputBox("Please enter your password")

Once entered, I need a macro to find that password in the hidden "Roster"
sheet of 200 users and return the user's name that is located in the next
column over.

 

[Bob Phillips]

With Worksheets("Roster").Range("A1:A200")
On Error Resume Next
Set cell = .Find(pWord, LookIn:=xlValues)
On Error Goto 0
If Not cell Is Nothing Then
MsgBox "Name is " & cell.Offset(0, 1).Value
Else
MsgBox "Invalid password"
End If
End With

 

[Guest]

I am just wondering if there is'nt some problem with your scheme. Normally
one would look up a login name with the corresponding password in a hidden
sheet and then compare that password with the supplied password.

In your system how will you ensure that the passwords are unique? This is
obviously required if for each password you want your routine to find a
specific user name. If you try and ensure uniqueness at the time users are
setting their passwords then what if the password cannot be set and the user
gets the message "This password is already in use and cannot be chosen.
Choose another" then would'nt that user know that the password which failed
is a valid password?

Alok Joshi

 

[Tom Ogilvy]

Getting enough answers?

 

[Guest]

thank you Bob!

With Worksheets("Roster").Range("A1:A200")
On Error Resume Next
Set cell = .Find(pWord, LookIn:=xlValues)
On Error Goto 0
If Not cell Is Nothing Then
MsgBox "Name is " & cell.Offset(0, 1).Value
Else
MsgBox "Invalid password"
End If
End With

 

[Guest]

Tom,
Thanks for your "other" reply, so quickly. Yeah, I had to leave for a
meeting and it was essential that I have a solution upon returning, so I
thought I'd pursue a couple of different avenues.