Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Functions that Change Other Cells
There seems to be some kind of security that prevents a function called from a worksheet changing other cells (on that sheet or anyother). Is there anyway to get around that? I'm hopeing to update a range of cells when the sheet calculates without using the "onCalcluate" event. In fact I know that it's possible because I use a thrid party product that does just this. How it does it is somewhat of a mistery to all of us. To hopefully make myself clearer if I call this function: Public Function timesTheyAreAChanging() As String timesTheyAreAChanging = "Time" Range("A3") = Now() End Function From a worksheet using: "=timesTheyAreAChanging()" then it returns an error result. Yet if I comment-out the line that changes another cell then it works fine. This is obviously a simple example what I want to do is much more complex but the problem remains the same. - Rm |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Functions that Change Other Cells
Functions can't change the values of cells other than those calling the
function...think about it , a function returns a value... There is a workaround, and that is to use the worksheet's events to call a procedure that can alter cells. You have a choice. A common one is the _Changed event that is fired when and velue is eneterd into a cell. care with this, because it gets fired whether the user enters a value or when code eneters a value, so you need to either disable events or devise another way to prevent endless loops if using code. You could also use the _Calculate event to test various cell values and thus fire off procedure calls. Again, be wary of endless loops. Private Sub Worksheet_Calculate() Dim calc_mode As Long calc_mode = Application.Calculation Application.Calculation = xlCalculationManual Application.EnableEvents = False ' add your tests here Select Case Range("TestCell").Value Case 0 Call Proc_ZERO Case Is < 0 Call Proc_LESSTHAN Case Is 0 Call Proc_POSITIVE End Select Application.EnableEvents = True Application.Calculation = calc_mode End Sub "Robert Mulroney" wrote: There seems to be some kind of security that prevents a function called from a worksheet changing other cells (on that sheet or anyother). Is there anyway to get around that? I'm hopeing to update a range of cells when the sheet calculates without using the "onCalcluate" event. In fact I know that it's possible because I use a thrid party product that does just this. How it does it is somewhat of a mistery to all of us. To hopefully make myself clearer if I call this function: Public Function timesTheyAreAChanging() As String timesTheyAreAChanging = "Time" Range("A3") = Now() End Function From a worksheet using: "=timesTheyAreAChanging()" then it returns an error result. Yet if I comment-out the line that changes another cell then it works fine. This is obviously a simple example what I want to do is much more complex but the problem remains the same. - Rm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to set up worksheet where cells change monthly | Excel Worksheet Functions | |||
how can i change the colum width for cells on the same worksheet | Excel Discussion (Misc queries) | |||
Worksheet Functions that Change Other Cells | Excel Programming | |||
How to make a function change other cells in worksheet | Excel Programming | |||
worksheet functions change to values? Formula are text? | Excel Programming |