Cell Validation List from a UDF

[Adamski]

Hello,

Is it possible to use a UDF for data validation? If so how. It seems to consider everything a named range - unless I'm doing it wrong. I thought a function returning a 1D range, or an array of strings would be possible.

Cheers

[Bob Phillips]

Not exactly clear as to what you want the UDF to do. DV basically tests for a true or false condition, so do you want to pass the cell to the UDF and do that work there?

[Adamski]

At the moment I have Validation set up to list values from a Named Range in a drop down for the user to pick from. I want the same functionality but without the need for the Named Range - the list items instead supplied form a udf (not a static list).

[lucas]

Why not just make the named range dynamic so that if you add items it expands to include them?

[lucas]

Another advantage of a dynamic named range is that it can be on a different sheet.


see attached

[Adamski]

Thanks, but the reason I wanted to see if it could be done with a function is that my NamedRange (which is working well) needs to use the Index function. This causes the workbook to be Dirty on opening and so prompts to save changes even when none are made.

If List data must come from either a range or a static list, I could execute a macro OnChange to set the NamedRange (without needing the Index function).

Just thought I could maybe do it with a UDF in Validation instead. It's Excel 2003 by the way.

Cheers,

[Bob Phillips]

Index is not volatile, so it should not make the workbook volatile. unless that is you are using a form such as A$2:INDEX(A$2:A$20,some_rownum) , in which case it does seem to becme volatile.

But I still don't get where the UDF will get these values from to pass back, and anyway, I don't believe you can use UDFs in DV.

[Adamski]

Yes, I am using Index as the second part of a range reference which is flagged as dirty when the workbook is opened. The UDF would do the same thing as my current NamedRange - it would return the same data but without needing the Index function.

Anyway, I also don't think it is possible to use a UDF in Data Validation.

[lucas]

Why not just set it up so that when you close the workbook it closes without alerts and doesn't save changes?

[Adamski]

Yeah, it's all fine so long as it is opened with macros enabled. It's only a minor issue I was hoping to fix - not important really.

Cheers

[lucas]

But you were wanting a UDF solution which would require macro's to be enabled too.......??

[Adamski]

Yep, the workbook wouldn't work - that's expected. But it also wouldn't prompt to save.

[austenr]

I like Lucas example but how do you get to the refers to in data validation in 2007? I don't see it...Also, can you dynamically sort a new entry to the range?