VBA CALCULATE ON SHEET CHANGE: trying to get book to calculate on sheet change

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,786
Office Version
  1. 2016
Platform
  1. Windows
When I change to any sheet I would like the entire workbook to recalculate.
I have a table that has sizes on it and then that table populates about 100 forms but when I change sheets i have to press F9 to get the sheet to calculate to show the sizes...
I have Automatic calculate on plus I have checked all the macros and they all have as the last task Application.Calculation = xlAutomatic.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sounds odd. When you first opened Excel was the first sheet you opened set to manual calculate? Excel remembers the calculation state of the first workbook you opened and uses that as a default. it's cured by shutting Excel and reopening using a workbook with calculation set to automatic

If you put this in the ThisWorkbook object in your Personal.xlsb it should work. However it will work no matter what workbook you've got open, so if you've any that should be manual or that take a long time to calculate you might want to take the second option

Option 1:
Code:
Option Explicit
Public WithEvents App As Application
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Set App = Application
    App.Calculate
End Sub

Option 2:
Put this into the Code section for every worksheet you want it to happen on
Code:
Private Sub Worksheet_Activate()
    Application.Calculate
End Sub
 
Last edited:
Upvote 0
Johnny C--thanks.

1st I made sure I saved three workbooks with Autocalculate on, and put the Option 1 code in all three. Then opened them and selected a sheet but it did not calculate.
2nd, I did Option 2 to about five sheets and it worked perfectly, so that is the route I am taking...

Erv
 
Upvote 0
For the first one you have to put it into Personal.xlsb, not the sheets themselves.

it can be a bit temperamental, I use it to automatically turn NumLock on as we've got a rogue version on Excel with an add-in to connect to a SQL Server Db on Citrix that randomly turns it off. It didn't work the first few times I tried it but eventually it started working (for no reason)

Code:
Option Explicit
Public WithEvents App As Application
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Set App = Application
    Application.Calculate
End Sub
Private Sub Workbook_Open()
    Set App = Application
End Sub
Private Sub App_SheetCalculate(ByVal Sh As Object)
    Set App = Application
End Sub

You might need to add the 2 extra bits, I had to to get it working. There's no reason you should need them but I did.
 
Upvote 0
now I need code that lists all sheets that have code on them...so I can delete the code off those sheets...
 
Upvote 0
One thing you could try ...
put this code into the ThisWorkbook object for the workbook
Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.Calculate
MsgBox "Change sheet"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.Calculate
MsgBox "Change value"
End Sub
Delete the code on the worksheet and see if the code runs when you change a value in that sheet without code, or change worksheets

You may need to try changing a value and then swapping worksheets to another one and swapping back, I just ran those and both worked but only after I'd done that

If so you can delete all the code from the worksheets (and delete the msgbox code)
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,727
Members
448,294
Latest member
jmjmjmjmjmjm

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