worksheetfunction.vlookup result when argument not found

[conor]

I am trying to execute the piece of code below. The user initials (UserInits) have already been successfully entered thru an InputBox. The initials are then checked against a range (Users) in the workbook to determine what the matching entry should be in the next InputBox which will ask for the password. This code works if the vlookup finds a match on user initials. If it does to find a match I get "run-time error 1004 application-defined or object-defined error". The watch window tells me the value of password has become <out of context>. I expected it to return #N/A as it does on a worksheet, but it doesn't.


password = WorksheetFunction.VLookup(UserInits, Users, 2, False)


Does anyone have any input. Thanks in advance

 

[Norie]

Why don't you use a userform rather than inputboxes?

Then for the user initials you could use an combobox populated from the spreadsheet.

This would make sure users could only select existing values.

For the password you could use a textbox, and you would have the added advantage of being able to hide the password using it's PasswordChar property.

 

[conor]

to Norie

Thanks for the quick input. I'm not VB guru, so I will have to investigate the userform. I really don't know what that is. But this is the second time I have tried to write a function using a call to the vlookup function and the second time I have failed for the very same reason - it does not seem to return the value #N/A when the search argument is not found. Do you by any chance know what IS returned so I can validate it?

 

[Norie]

Using VLookUp, or any function that can produce errors, in VBA can be difficult.

I don't actually know what's returned when a value isn't found, I rarely if
ever use VLookUp in VBA.

I'll try and find out and post back.

Meanwhile if you want to try the userform idea try this.

1 Goto the VBA Editor.(ALT+F11)

2 Goto Insert>UserForm.

3 From the toolbox click on combobox then draw one on the form.

4 Do the same for a textbox.

5 Goto View>Properties.

6 Select the combobox, set the ColumnCount to 2 and the RowSource to Users.

7 Select the textbox and set the PasswordChar property to *.

8 Now run the userform from the Run menu.

You should then see a userform that has a combobox listing in one column user initials and in another the passwords.

If you type anything in the textbox all you will see will be *s.

Note this is only a start, you'll probably want to stick a couple of buttons on the form too and you probably want to hide the passwords.

By the way this assumes Users is a named range with 2 columns, one for initials and one for password.

 

[conor]

Dude,

That is way more help than I ever expected on a public forum! You are THE MAN (or THE WOMAN)! Norie isn't a name I can associate with either gender. Thank you A LOT. And yes, the lookup range is only 2 columns. I'll test this out as soon as I say thank you. First things first.

Thank you.

conor