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

[itr674]

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.

 

[Johnny C]

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:

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

Private Sub Worksheet_Activate()
    Application.Calculate
End Sub

 

[itr674]

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

 

[Johnny C]

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)

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.

 

[itr674]

now I need code that lists all sheets that have code on them...so I can delete the code off those sheets...

 

[Johnny C]

One thing you could try ...
put this code into the ThisWorkbook object for the workbook

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)