 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2008How to keep a formula from being over written?
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 >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 [quoted text clipped - 23 lines] > > josh Hi Josh, Maybe you should describe what goals you're trying to achieve; there may be alternative solutions  SignatureKind regards, Niek Otten Microsoft MVP - Excel | 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 [quoted text clipped - 29 lines] | > | > 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 > Hi Josh, > [quoted text clipped - 38 lines] > | > > | > 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. SignatureKind 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 [quoted text clipped - 51 lines] | > | > | > | > 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 > 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 [quoted text clipped - 29 lines] >> >> 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 SignatureKind regards, Niek Otten Microsoft MVP - Excel | Tim, | [quoted text clipped - 38 lines] | >> | >> josh Please see my original posting. > Hi Josh, > [quoted text clipped - 50 lines] > | >> > | >> 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. SignatureKind regards, Niek Otten Microsoft MVP - Excel | Please see my original posting. | [quoted text clipped - 52 lines] | > | >> | > | >> josh Thanks for the heads up Niek. > Hi Josh, > [quoted text clipped - 79 lines] > | > | >> > | > | >> josh |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.