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

macca_18380

New Member
Joined
May 15, 2024
Messages
22
Office Version
  1. 365
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Why don't you keep the calculation manual on and press F9 when you need to recalculate?
 
Upvote 0
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.
 
Upvote 0
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.
VBA Code:
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
 
Last edited:
Upvote 0
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.
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top