Custom Data Validation with UDF

Question

I created a UDF to analyze a character string (credit card number) and calculate whether or not it is a valid card number (called a Mod test). When I use the UDF in excel, it runs just fine! (yay...) The UDF is a boolean, so it just passes back true/false at the end of the calculation.

I'm trying to figure out if/how I can use the UDF as a custom data validation rule, so that I can set up validation on a range of cells within a spreadsheet.

I've tried to use the rule =UDF(F9)=TRUE, and various permutations, but the farthest I get is an error message that says "A named range you specified cannot be found." If I omit the initial equals sign, I don't get the error message but everything comes back as False, including strings that I know are valid. So, it's just not running.

Funny that it runs when I call the function from a cell formula but I can't get it to work within the data validation feature.

Any suggestions?

Thanks!

Answer 1

How about using conditional formatting rather than validation?

Select the range to test (say the first cell is B5), use rule =NOT(MyUDF(B5)) and format with, say, red fill.

Now all cells that Fail your test are red

best wishes

Answer 2

Thnaks for the feedback; glad my idea was useful.

Answer 3

Bernard, thank you, that worked for me!  The opposite rule works nicely as well:

Rule =MyUDF(B5) and format with green fill or some other color, for the cells that pass.

I wanted to make the Data Validation approach work for me, but sometimes you take an eight-iron and let the club do the work!

Thanks, again, for the elegant solution!

Regards, Tim

Answer 4

Thanks, Bernard!  I'll explore that option.

Regards, Tim