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
| Please see my original posting.
|
|
|
|
| | > 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
| >
| > | > | 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
| > | >
| > | >
| > | > | > | >>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
| > | >>
| > | >>
| > | >>
| > | >
| > | >
| > |
| > |
| >
| >
|
|