PDA

View Full Version : manual calculating



ashgull80
02-17-2008, 11:41 AM
hi
is it possible to set the formulas on one sheet to manual instead of setting the whole workbook to manual?
thanks ash

Bob Phillips
02-17-2008, 11:49 AM
Set it to manual on activating that sheet and automatic on deactivating.

Simon Lloyd
02-17-2008, 11:50 AM
Yes you can, add this to the worksheet module you want manual calculation on!

Private Sub Worksheet_Activate()
Application.Calculation = xlManual
End Sub
Private Sub Worksheet_Deactivate()
Application.Calculation = xlAutomatic
End Sub

Simon Lloyd
02-17-2008, 11:51 AM
haha!.....errr you mean like that Bob?

ashgull80
02-17-2008, 11:54 AM
so how does this work if i wish to have it on manual all the time apart from when a chart is viewed? i will most likely open the chart from a userform.
thank you for your help.
ash

Bob Phillips
02-17-2008, 11:54 AM
Something like that Simon :yes

Bob Phillips
02-17-2008, 11:55 AM
Then set to manual before opening the chart (whatever that means) and automatic after.

Simon Lloyd
02-17-2008, 12:02 PM
Private Sub Worksheet_Activate()
Application.Calculation = xlAutomatic
End Sub
Private Sub Worksheet_Deactivate()
Application.Calculation = xlManual
End Sub
just swapped around slightly but put this in the worksheet module that houses the chart.

ashgull80
02-17-2008, 01:30 PM
so if i have a chart in sheet 1 that gets its data from sheet 2, sheet 2 needs to be set to manual, and then a userform opens the chart, which then needs to recalculate sheet 2 and display the new data. while the rest of the book is still on automatic.
how would the code for that go?
sorry bit of a novice.
thanks ash

Bob Phillips
02-17-2008, 01:57 PM
Why does anything need to be set to manual at all?

ashgull80
02-17-2008, 02:03 PM
because i have so many calculations everytime a userfomr is opened i take 5mins 2 load as it recalculating the whole page, what i have is a page with takings for the year, i want a line graph to show to show takings over 52 weeks, so on a seperate page i have 2007 week 1 values, 2007 week 2 values etc etc for the next 10 years, but i have that with income, and expenses
so there are over 1000 formulas.
is there a better way i could do it?
thanks

ashgull80
02-17-2008, 04:17 PM
i guess i could code so when the chart is opened it filters the data, so 2008 data is shown then displayed on the chart and when the chart is closed the data is unfiltered. do u think thats a better idea, would save the need for a seperate sheet.
ash

Simon Lloyd
02-18-2008, 03:13 AM
well in that case why not only calculate a certain range when opening sheet1?, like this:
Private Sub Worksheet_Activate()
Range("A1:C10").Calculate
End Sub

ashgull80
02-23-2008, 04:50 PM
so how would that formula work on a comand button, would i just do the put something like

Private Sub commandbutton1_click ()
Range("sheet1!A1:C10").Calculate
End Sub

Bob Phillips
02-24-2008, 02:40 AM
Yes, but I would specify the sheet as well.

Simon Lloyd
02-24-2008, 05:44 AM
xld means like this:

Sheets("Sheet1").Range("A1:A100").Calculate