Detecting if a Form input matched a given list within the excel document

Hi all, Thanks in advance,

I am trying to have the below have a section in it that when the user click out the form input, it checks to see if the input matches one of the entries within a given sheet and also matches a pre-determined formatting

the formatting should be AAA1, but the letters and numbers can be anything from A>Z and 1>9

The sheet for checking against will be called "Locations"

Please Login or Register  to view this content.

Why not use a ComboBox with a specified drop down list. That way you can easily control what values can be selected and not have to test for formatting etc.

BSB

While that would work for myself, I need to make it so that someone without experience can add and remove from the listing to support those that have no VBA knowledge.

This is to stop someone causing an error by changing something they shouldn't.

Hmmm... Not sure that reason is valid as users wouldn't need to go anywhere near the VBA. If the valid values for the list are to be in a range on a sheet that is accessible to all users then they would be able to add and remove values without issue. THe VBA can then apply the drop down list based on that list automatically.

But, if you don't want to do it that way then you'll have some slightly more complicated VBA to write.

You'll need to convert the input into uppder case, then test the current string length as the user types. If length = 1, 2 or 3 then only allow the keyasciii codes for letters A-Z, if length = 4 then only allow the keyascii for numbers 0-9. If length is greater than 4 then prevent further input before checking if the input appears in a range of accepted values and warn the user if not. Sure seems like a lot of work when a ComboBox could do all this for you.

Happy to help you with the code if you can attach a sample workbook showing data layout for the list to compare the input against and the form layout.

BSB

Hastily thrown together sample file attached for you to see one way of accomplishing this (still not the best solution in my opinion).

Code used is below. It will obviously need amending so sheet and range names fit with your file.

Please Login or Register  to view this content.

BSB

Have sent a doc though private due to not feeling comfortable showing on main