If cell value does not match a value from a list?

[smaier69]

Greetings, all!

I am trying to find a way to prevent employees from incorrectly typing in their employee number. I can do it caveman-style by using:

Please Login or Register  to view this content.

I would have to make a new "If" segment for every employee, though. If I could use "Or" (<> 1000 Or 1204 Or 5492, etc.) it would seem pretty straightforward. And since the employee numbers aren't in tight sequence I can't just use > 1000 and 5000 < as it would still allow for typing in an nonexistent employee number within that range.

Is there an easy way to add an array of acceptable numbers within a single If command?

[sbkeenan]

Hi Smaier69

Do you need to use VBA?

You could simply have all the employee numbers in a list on another tab (or hidden somewhere on teh same tab) and use data validation to set up an in-cell dropdown. Alternatively, leave out the in-cell dropdown so that the user can type in their number. If it isn't in the list, Excel, will throw up a message box.

Does his help?

Regards
Stephen.

[smaier69]

Hello, sbkeenan!

Unfortunately, yes it needs to be VBA as this check is part of a larger userform/macro where employees enter their applied labor data. The list of employee numbers is in a dropdown in the userform, but for whatever reason some folks prefer to type in their number and not pay attention all at the same time.

I like your idea, though. Maybe I can use VBA to check a worksheet within the workbook that contains the list of employee numbers?

[smaier69]

OK, I'm officially a fool. :p

It's been long enough since I've tweaked the VBA in this, I completely forgot the core check mechanism... userform data gets copied into a separate tab where whatever checks (errors in times, part number or sales order lengths and so forth) take place, then that data gets pushed into the master/database. Following that, the line that was used as a temporary checkpoint gets cleared.

So, when I tried my original "If Cells(19, 4).Value <> 1000 and 1045 and 4432 and 3423 (etc including all valid employee numbers) Then" code, I would start out with an intentionally incorrect number to see if I got the error msgbox. After getting the error msgbox I would try a valid employee number only to get the same error msgbox. This because the macro hadn't completed so the cell value being checked still contained the invalid employee number.

All I needed to do was add "ActiveSheet.Range("A1:S1").Delete" following the msgbox and it worked.

I deserve to be horsewhipped. Thanks for not laughing at me