CODEWITHSUNDEEP
vbaexcel
Alex Cavanaugh
Alex Cavanaugh

Reputation: 435

How to stop charts from auto-updating in excel

I have a macro that runs a generates a new sheet and populates it with various graphs. The data for these graphs comes from sheet2. I want to be able to delete sheet2 after generating the graphs without losing all the information in the graphs. So far I've tried setting calculations to manual, protecting the sheet, and protecting the charts using both the ProtectData and ProtectSelection methods. All of the above has failed. Is there any way using vba to stop charts from changing after they're generated? Thanks.

Edit: An answer suggested populating an array with values from a range then using that array to generate a chart. Now I'm running into a type mismatch error when trying to generate the chart. Is it possible to set the source data equal to an array? Here's my code so far:

Dim PlotRangeBar() As Long
Dim PlotRangePie As Variant
Dim XValPie As Variant
Dim XRangeBar As Range
Dim CellCount As Long

'Create bar graph
Set XRangeBar = ActiveWorkbook.Sheets(2).Range("B" & DataStart & ":B" & DataEnd)
i = 0
For Row = DataStart To DataEnd
    If Cells(Row, UsedColTimesheet).FormulaR1C1 <> "0" And Cells(Row, UsedColTimesheet) <> vbNullString Then
        ReDim Preserve PlotRangeBar(i)
        PlotRangeBar(i) = Cells(Row, UsedColTimesheet).Value
        i = i + 1
    End If
Next

ActiveWorkbook.Sheets(Sheets.Count).Select
ActiveSheet.Shapes.AddChart.Select
    With ActiveChart
        .ChartType = xlColumnStacked
        .SetSourceData Source:=PlotRangeBar
        .SeriesCollection.NewSeries
        .SeriesCollection(1).XValues = XRangeBar
        .SetElement (msoElementChartTitleCenteredOverlay)
        .ApplyLayout (1)
        .ChartTitle.Text = ResourceName & " - Hours per project"
        .Legend.Delete
        .ChartStyle = 18
        .ProtectSelection = True
    End With

Upvotes: 2

Views: 2666

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Load your data into an array and use the arrays to load your charts when you create them.

Upvotes: 3

Related Questions