Thanks for the heads up Niek.




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

Indeed it does write into the cell from which it was called.

That's very unusual. Normally Excel prohibits changing anything in a
workbook (and it is quite right doing so) but this seems
another glitch I wasn't aware of up to now; there are a few others.

Nonetheless, functions are supposed to do nothing but replace their call
with a return value, based on the arguments of the call.
There is some discussion whether they should be allowed to access (read)
worksheet areas (directly, not via the argument list)
that do not change and it seems the purists win. I'm not a .Net expert,
but I think you can't in its language family.

So, apart from what does and doesn't happen to work, I strongly advise not
to try and change worksheets from a function that is
called from a worksheet; they're just not meant to do that and might stop
doing so without any warning any future release.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Josh Sale" <jsale@tril dot cod wrote in message
...
| Please see my original posting.
|
|
|
|
| "Niek Otten" wrote in message
| ...
| Hi Josh,
|
| <I've written a UDF that adds a validation to the cell it was called
from
| and it works just fine
|
| Please show the code of that UDF
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Josh Sale" <jsale@tril dot cod wrote in message
| ...
| | Tim,
| |
| | Thanks for the reply, but I beg to differ. I've written a UDF that
adds
| a
| | validation to the cell it was called from and it works just fine ...
up
| | until the point in time that the user uses the validation to change
the
| | cell's value ... at which point the UDF is replaced by its value.
| |
| | josh
| |
| |
| |
| | "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
| |
| |
| |
| |
| |
| |
| |
|
|
|
|