PDA

View Full Version : Solved: Checking Cell in Range



shades
07-13-2004, 08:57 AM
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
07-13-2004, 09:48 AM
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
07-13-2004, 10:00 AM
Thanks. There will be multiple users (20+), each with a unique password.

CBrine
07-13-2004, 10:07 AM
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
07-13-2004, 10:13 AM
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
07-13-2004, 10:31 AM
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
07-13-2004, 10:56 AM
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
07-13-2004, 11:02 AM
Glad I could help :)

CBrine
07-13-2004, 11:13 AM
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
07-13-2004, 11:21 AM
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
07-13-2004, 11:40 AM
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
07-13-2004, 11:58 AM
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 :dunno ).

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
07-13-2004, 12:09 PM
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
07-13-2004, 12:26 PM
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
07-13-2004, 01:28 PM
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
07-13-2004, 01:51 PM
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! :D :yes

Zack Barresse
07-13-2004, 01:54 PM
... 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
07-13-2004, 02:00 PM
Man ya take a nap and ya miss all the fun, bummer.

CBrine
07-13-2004, 02:11 PM
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
11-16-2004, 11:45 AM
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

johnske
11-16-2004, 01:29 PM
Hi Shades,

Going back to your original code, a slight mod with an "On Error GoTo" givesSub PassWordTest()
Dim LastName As String, PassWord As String
Sheets("Passwords").Range("H4").Value = InputBox("Enter Last Name")
On Error GoTo Msg
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
End If
Msg: MsgBox ("Your Name is not listed")
End Sub
Which would seem to do the job. But maybe I'm missing something here? :bink: