Cell changes that trigger subroutines

**TheKid** · 03-18-2009, 12:22 PM

Hello,

I am looking to have a change in value of a cell trigger some range cut and pasting. The change in the cell will be driven by the calculation tree (ie it will not be a manual change)

However, Excel does not allow subroutines to be called from cells, so I'm not sure how to achieve this.

Your help is much appreciated

**TheKid** · 03-18-2009, 12:29 PM

I'll add that I've managed to hack this by highjacking the SheetCalculate event and checking there if the value of the cell has changed to a value that warrants the cut and paste action, but I'm hoping for a solution without call overhead every time the sheet caculates. (My sheet receives live data and hence can recalculate many many times per second...)

**Richard Buttrey** · 03-18-2009, 12:42 PM

Hi,

One way might be to set the Calculation mode to manual and then have the following code in the Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Range)
Dim st as String    
    Application.Calculation = xlCalculationManual
    st = Range("A1")
    Application.Calculate
    If Range("A1") <> st Then 
        'your code
    End If
End Sub

You could put Application.Calculation=xlCalculationAutomatic in the Deactivate event of the worksheet, (and perhaps the xlCalculationManual in the Activate event), to avoid leaving the calculation set to manual

HTH

**TheKid** · 03-18-2009, 12:54 PM

Thanks Richard, I like your thinking but this solution is very similar to the hack I've already done. Both solutions have to check the state of the cell in question on every change to the sheet.
The problem is that there are potentially thousands of sheet changes per second that won't require the action so checking every time if the action is required significantly reduces the data volume I can pass through the sheet.

I am hoping to somehow latch onto the cell in question changing and sit still doing absolutely nothing untill it does change.