| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connumdrum #2
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Connumdrum #2
=myFunction
is a user defined name =myFunction() is a function Is that your problem? -- Regards, Tom Ogilvy "Alex J" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Connumdrum #2
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 "Tom Ogilvy" wrote in message ... =myFunction is a user defined name =myFunction() is a function Is that your problem? -- Regards, Tom Ogilvy "Alex J" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Connumdrum #2
I can reproduce it, but I haven't figured out the cause.
-- Regards, Tom Ogilvy "Alex@JPCS" wrote in message ... 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 "Tom Ogilvy" wrote in message ... =myFunction is a user defined name =myFunction() is a function Is that your problem? -- Regards, Tom Ogilvy "Alex J" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Connumdrum #2
OK, it isn't supported:
http://support.microsoft.com/default...23&Product=xlw XL97: Cannot Use Custom Function with Data Validation I suspect this hasn't changed in later versions. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... I can reproduce it, but I haven't figured out the cause. -- Regards, Tom Ogilvy "Alex@JPCS" wrote in message ... 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 "Tom Ogilvy" wrote in message ... =myFunction is a user defined name =myFunction() is a function Is that your problem? -- Regards, Tom Ogilvy "Alex J" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Connumdrum #2
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 "Tom Ogilvy" wrote in message ... OK, it isn't supported: http://support.microsoft.com/default...23&Product=xlw XL97: Cannot Use Custom Function with Data Validation I suspect this hasn't changed in later versions. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... I can reproduce it, but I haven't figured out the cause. -- Regards, Tom Ogilvy "Alex@JPCS" wrote in message ... 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 "Tom Ogilvy" wrote in message ... =myFunction is a user defined name =myFunction() is a function Is that your problem? -- Regards, Tom Ogilvy "Alex J" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Connumdrum #2
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. Alex J wrote: 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 "Tom Ogilvy" wrote in message ... OK, it isn't supported: http://support.microsoft.com/default...23&Product=xlw XL97: Cannot Use Custom Function with Data Validation I suspect this hasn't changed in later versions. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... I can reproduce it, but I haven't figured out the cause. -- Regards, Tom Ogilvy "Alex@JPCS" wrote in message ... 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 "Tom Ogilvy" wrote in message ... =myFunction is a user defined name =myFunction() is a function Is that your problem? -- Regards, Tom Ogilvy "Alex J" wrote in message ... 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 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Connumdrum #2
And if I had click on the link to the kb article, I would have seen that it
describes this. oops. Dave Peterson wrote: 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. Alex J wrote: 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 "Tom Ogilvy" wrote in message ... OK, it isn't supported: http://support.microsoft.com/default...23&Product=xlw XL97: Cannot Use Custom Function with Data Validation I suspect this hasn't changed in later versions. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... I can reproduce it, but I haven't figured out the cause. -- Regards, Tom Ogilvy "Alex@JPCS" wrote in message ... 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 "Tom Ogilvy" wrote in message ... =myFunction is a user defined name =myFunction() is a function Is that your problem? -- Regards, Tom Ogilvy "Alex J" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Connumdrum #1 | Excel Programming | |||
Linear Mode
