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

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

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

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

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

"Josh Sale" <jsale@tril dot cod wrote in message ...
|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
| |
| |
| |
| |
| |
|
|
|
|

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

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

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

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