VBA To Run when changing worksheets

[Simon.Ward]

Hi,

I have set a Auto_Open VBA code to switch off Calculations when opening a large workbook which works fine.

However i would now like to calculate each worksheet as i select that individual worksheet

I know you can use the

Please Login or Register  to view this content.

code but this requires the amendment of a specified cell on that worksheet to activate the code, i want it to happen automatically when opening that worksheet without having to amend any cell.

Can this be done?

Thanks

Simon

[JosephP]

use the worksheet_activate event or probably easier to use the workbook_sheetactivate event in the thisworkbook module

[Simon.Ward]

Great thanks that worked.

Also i have this code to run calculations on teh activesheet when a change occurs on that sheet but also to then open another sheet to run calculations on, the first calculation works great but it is actually quicker for me to just go to the Store Balance Sheet tab and click Formulas/Calculate Sheet.

Is it just my code thats making it slower?

Please Login or Register  to view this content.

Many Thanks

Simon

[JosephP]

if you have sheetactivate code in place then activating the sheet and then calculating it is a double calculation-use

Please Login or Register  to view this content.

[Simon.Ward]

Thanks

The trouble is that there are many sheets in the workbook which each need calculating but i only want to calculate the Private Sub Worksheet and the Store Balance Sheet worksheet hence the Activesheet statement i used. The code you gave above calculates the whole workbook....can u specify only Store Balance Sheet somehow?

Thanks again

[JosephP]

the code above specifies the two sheets to calculate

[Simon.Ward]

Ah ok i thought it was doing the lot but its because the calculations on the store balance sheet are so plentyful it takes a little while

Thanks for you help