I want to create a very easy way for my users to add database driven
validation lists to worksheets. So somebody says I want such a validation
list in (say) cell B2, they add the UDF to that cell (=foo(some args)),
foo() gets the list of values from the database and creates the validation
in B2. By recalculating the formula they should be able to get a fresh list
of values from the database.

Is this the kind of info you're asking for?

Thanks,

josh


"Niek Otten" wrote in message
...
Hi Josh,

Maybe you should describe what goals you're trying to achieve; there may
be alternative solutions

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
| There's no way to have a cell hold both a function and a value.
| In any case, a UDF cannot modify the cell it's in, so you can't use one
to
| create the validation list.
|
| Tim
|
|
| "Josh Sale" <jsale@tril dot cod wrote in message
| ...
| I want to create a UDF that when placed in a cell will create a
validation
| for that cell. The challenge is how to keep the UDF in the cell's
formula
| when the user makes a selection from the validation's drop-down list?
|
| So for example, I might have a UDF
|
| Function foo() As Variant
| With Application.Caller.Validation
| .Delete
| .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
| Operator:=xlBetween, Formula1:="a,b,c"
| .InCellDropdown = True
| End With
| foo = "a" ' make sure the cell starts with a valid selection
| End Function
|
| So somebody can then type "=foo" into cell A1 to have the validation
list
| added.
|
| However when the user makes a selection (e.g., b or c) from the
| validation, I don't want to lose "=foo" as the formula.
|
| Any suggestions?
|
| TIA,
|
| josh
|
|
|
|
|