Excel: deactivate an embedded chart?

[TheRobster]

Is it possible to deactivate an embedded chart (chart in a worksheet, not a chartsheet).

I have an embedded chart that has a large data source range (about 10,000 cells) and a VBA macro that performs some calculations and inputs the results into these cells, but the chart updates in realtime and it slows the macro down quite a lot.

I have read that the following code could be used if the chart is in its own chartsheet:

Private Sub Chart_Deactivate

But my chart is embedded, so how could I deactivate/reactivate it?

Alternatively, I thought I could change the data source range to some blank cells, then change it back again once the macro has finished. Does this sound like viable alternative, if there is no way to deactivate the chart?

I am mainly trying to think of ways to speed up the macro.

Thanks
-Rob

[JustinLabenne]

Can you post the code you are using or attach a sample?

Have you tried setting calculation to manual while the code is running?

[TheRobster]

If I set the worksheet the chart is embedded in to manual calculation, would that stop the chart from updating?

I thought that manual calculation only stopped formulas from updating, or does it also stop charts from updating?

[JustinLabenne]

I cannot really say for sure, never had a reason to try it and without knowing your code and why it actually happens for you, I can't replicate your problem so to speak. Post your code or better yet and attachement of the file in question.

[scottiebhoy]

I'm also experiencing a similar problem

I've a selection of embedded charts within the same worksheet, each of which is based on a pivot table. In order to allow the data to be dynamic as the pivot table results change, and i want to filter out certain values which fall below agreed parameters, i've written the following code to set the source data to the relevant chart

Sub Sort_Chart()
1.Worksheets("Dashboard").Activate
2.Range("Z6").Activate

3.Range(Range("Z6").End(xlDown), Range("AA6")).Select

4.ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Dashboard").Range(Range("Z6").End(xlDown), _
Range("AA6")), _
PlotBy:=xlColumns
End Sub

Problem is, having activated the first chart, i can't then move back onto the worksheet to set the next set of source data as the intitial chart remains active

Deactivate event doesn't appear to be working, any help on this would be greatly appreciated

Scott

[scottiebhoy]

Sorted

Using the following works and allows me to navigate through multiple charts, setting source data for each without a problem:

ActiveWindow.Visible = False

Might work for you also??

[anhn]

This will help:

Application.ScreenUpdating = False
'-- Updating your data here
Application.ScreenUpdating = True