How to turn on automatic calculation when a range of cells is changed, and then switch off again.

[macca_18380]

Hi All,

I have a workbook i'd like to keep in manual calculation except in the instance that a value in a range of cells changes.

So, for example if a cell in range A1 to D1 changes then i'd like to calculate the sheet and then turn off calculation afterwards.


Thank you
Michael

 

[Cubist]

Why don't you keep the calculation manual on and press F9 when you need to recalculate?

 

[macca_18380]

The end users of the spreadsheet are not sophisticated and struggle with this concept, i was hoping that this code could sit in the background and run without need for their intervention.

 

[Cubist]

I think telling people to press F9 to refresh the results isn't a hard concept to grasp. Anyhow, try this. To be inserted in the Sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    Dim IntersectRange As Range

    Set WatchRange = Me.Range("A1:D1")
    Set IntersectRange = Application.Intersect(Target, WatchRange)

    If Not IntersectRange Is Nothing Then
        Application.Calculate
    End If
End Sub

 

[macca_18380]

I think telling people to press F9 to refresh the results isn't a hard concept to grasp. Anyhow, try this. To be inserted in the Sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    Dim IntersectRange As Range

    Set WatchRange = Me.Range("A1:D1")
    Set IntersectRange = Application.Intersect(Target, WatchRange)

    If Not IntersectRange Is Nothing Then
        Application.Calculate
    End If
End Sub

Thank you so much for your help, this is wonderful.