PDA

View Full Version : Automatic Calculation when sheet tab clicked



Jase
08-09-2012, 06:15 AM
Hi All. I have a excel spreadsheet with certain marcos built in. I need a method to automatically calculate the sheet.
Such as;
Activesheet.CalculateFull
or an alternative to Ctrl +alt + F9

Ideally I would live to activate it when the user navigates using the sheet tabs in excel, so when the user clicks page 5 tab for example that sheet calculates. I could create buttons to go to next page/previous pages that trigger the calculation but ideally want it to go through users clicking on the excel sheet tabs. Can this be done by inserting a new module into the VBA?

:banghead: Thank you for reading!

Kenneth Hobs
08-09-2012, 06:28 AM
Welcome to the forum!

In ThisWorkbook object:
Private Sub Workbook_Open()
ActiveSheet.Calculate
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Calculate
End Sub

Jase
08-09-2012, 08:07 AM
Thanks Kenneth. I'm afriad the code doesn't work for my case! Is there anything stronger than the .calculate method. Crtl alt F9 works. is there a calculateRebuild or something similar?

Jase
08-09-2012, 08:16 AM
if a create a button

Application.CalculateFull

works; however I tried to fit this into your code, and it still won't automatically update when i open the page. Instead it works if i click on a cell and push enter

Private Sub Workbook_Open()
Application.CalculateFull
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Application.CalculateFull
End Sub

Sorry I am a complete newbie at this!!!

Kenneth Hobs
08-09-2012, 11:12 AM
In the VBE, did you doubleclick ThisWorkbook and paste the workbook code that you last posted in that object?

Jase
08-10-2012, 01:15 AM
Yes, double clicked the thisworkbook object and inserted the code! :banghead:

Kenneth Hobs
08-10-2012, 05:14 AM
Post a short example workbook and tell us how it needs to work if you like.