How to keep a formula from being over written?

[Josh Sale]

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

 

[Tim Williams]

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

 

[Niek Otten]

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
|
|
| | >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
| >
| >
| >
|
|

 

[Josh Sale]

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

 

[Josh Sale]

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]

Hi Josh,

Why not use a Sub instead of a UDF? Add a menu item which does exactly what you describe. But don't use a Function for what is was
not meant for.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|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
|
|
| | > 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
| > |
| > |
| > | | > | >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
| > | >
| > | >
| > | >
| > |
| > |
| >
| >
|
|

 

[Niek Otten]

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
| >>
| >>
| >>
| >
| >
|
|

 

[Josh Sale]

Please see my original posting.

 

[Niek Otten]

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
| > | >>
| > | >>
| > | >>
| > | >
| > | >
| > |
| > |
| >
| >
|
|

 

[Josh Sale]

Thanks for the heads up Niek.

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
| > | >>
| > | >>
| > | >>
| > | >
| > | >
| > |
| > |
| >
| >
|
|