trick validation to take values from vba formula
 

I want to populate the cell input validation list from a vba function
(which will select its values (depending on other user inputs) from an
extensive external database that I don't want to replicate in excel).
I know there are some tricks you can play with the validation - eg.
dynamic ranges and even conditional expressions but I can't find a way
to link to a vba function. I've tried defining a (psuedo) named range
that is defined by a function but that doesn't work - maybe I'm just
returning the wrong syntax from my function?

Any ideas much appreciated!! I know I could use a combobox but that
seems a bit messy across a reasonably large number of columns.

Thanks a lot,
Andrew

trick validation to take values from vba formula
 

Here's what I ended up doing:

1) Create a dynamic named range called filter (ie. number of rows will
shrink/grow depending on the number of entries)
2) Link all of the validation cells to the filter range (even though
they should show different lists - don't worry about it at the moment)
2) Use WorkSheet_SelectionChange event to check if user clicks on one
of the cells that should have a validation and based on the type of
input required (determined by other cell entries above) query database
and write results to filter range

So the link between the list and the validation is fixed but the
entries in the validation change. I initially tried recreating the
validation each time but this seemed to cause some problems (possibly
because my lists were too long).

Hope this is of help.
Andrew


On 7 Apr, 15:44, wrote:
I want to populate the cell input validation list from a vba function
(which will select its values (depending on other user inputs) from an
extensive external database that I don't want to replicate in excel).
I know there are some tricks you can play with the validation - eg.
dynamic ranges and even conditional expressions but I can't find a way
to link to a vba function. *I've tried defining a (psuedo) named range
that is defined by a function but that doesn't work - maybe I'm just
returning the wrong syntax from my function?

Any ideas much appreciated!! *I know I could use a combobox but that
seems a bit messy across a reasonably large number of columns.

Thanks a lot,
Andrew