Connumdrum #2

  • Thread starter Thread starter Alex J
  • Start date Start date
A

Alex J

All,
I am trying to use a user defined function in the validation for a cell
range. The validation type is custom, and I have a function which is defined
as Boolean.

I get an error message saying that the referenced named range is invalid. I
am not using a named range!

Should user defined functions work in validation?

Thanks,
Alex J
 
Tom,



I am using:



Function ID_Frontlog(projno) As Boolean

ID_Frontlog = False

On Error GoTo XIT

If Right(projno, 2) = " F" Or _

Right(projno, 2) = " P" Or _

Right(projno, 2) = " E" Then

ID_Frontlog = True

End If



XIT:

End Function

This function works in code, and works when entered as a formula directly on
the sheet.

I am trying to set the custom validation Formula in the validation dialog
(not via VBA) as:

=ID_Frontlog(B9)

where B9 contains the text value "00OLE001 F". The error message is:

"A named range you specified cannot be found"



I know that it is easy enough to put the conditions directly into the
validation, however since the ID_Frontlog function is also used for other
verification in my VBA routines, the attraction is to only have to update
conditions in the function once, rather than going to the sheet.

Your advice would be greatly appreciated.

Alex
 
Thanks for digging that up, Tom. At least I know that I'm not doing some
dumb-ass thing (again)!

Tom, you are a really phenomenol performer on this NG. Your contributions
are always rock-solid, and very timely. I appreciate your help.

Alex J
 
One workaround would be to use a helper cell (maybe right next to the real cell
but in a hidden row/column). Put your udf formula there and have the
data|validation point at that cell.
 
And if I had click on the link to the kb article, I would have seen that it
describes this.

oops.
 
Back
Top