Creating a graph using a macro

[Moxioron]

Hello all.

What I am trying to do: Display a graph for a category when a user selects the 'Graph It' button. The graph would contain the dollars spent for that category.

I built the macro so it first deletes the graph displayed and then rebuilds a graph for the category the user selects the button for. The problem is the Chart name changes everytime (Chart 1, Chart 2, etc.).

What can I do to address the chart name issue? Thanks for your help.


ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Parent.Delete
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveSheet.Shapes("Chart 10").Name = "Chart 1"
Range("B28").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").IncrementLeft -113.4
ActiveSheet.Shapes("Chart 1").IncrementTop 195
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").Width = 550.8
ActiveWindow.SmallScroll Down:=12
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Roll-up'!$A$6"
ActiveChart.SeriesCollection(1).Values = _
"='Roll-up'!$G$6,'Roll-up'!$J$6,'Roll-up'!$M$6,'Roll-up'!$P$6,'Roll-up'!$S$6"
ActiveChart.SeriesCollection(1).XValues = "='Roll-up'!$B$43:$B$54"
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.SmallScroll Down:=-12

 

[Misca]

Instead of actual name you can also refer to charts with index numbers:

Sub ChartNames()

Dim i As Long


For i = 1 To ActiveSheet.ChartObjects.Count


    With ActiveSheet.ChartObjects(i)
        .Activate
        MsgBox .Name
    End With


Next i


End Sub

If you really want to learn about charts and charting techniques (with or without VBA) check out Jon Peltier's excellent site:
http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

 

[Moxioron]

Thank you for responding.

Please excuse my ignorance. Where would I insert the code you have above?

 

[Misca]

The macro above is quite useless: The code selects all the chart objects on your sheet one by one and the MsgBox shows the name of the selected chart object. I only wrote the macro to show you how you can refer to the objects by their index number (should've added the msgbox to display the index number as well).

Try pasteing this to your module (=where you copied your original code) and see if this does what you're after:

Sub ChangeChartDataSeries()
With ActiveSheet.ChartObjects(1).Chart
'Removes old series:
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop
'Adds new series:
    .SeriesCollection.NewSeries
    
    With .SeriesCollection(1)
        .Name = "='Roll-up'!$A$6"
        .Values = "='Roll-up'!$G$6,'Roll-up'!$J$6,'Roll-up'!$M$6,'Roll-up'!$P$6,'Roll-up'!$S$6"
        .XValues = "='Roll-up'!$B$43:$B$54"
    End With
    
    .ChartType = xlLine
    
End With
End Sub

Instead of deleting the old chart it only clears the old data series and replaces them with a new one.
So instead of using the index you should be able to use the chart name as well: It should stay the same.
Also, the .ChartType=xlLine does nothing there unless the original chart was not a line chart.

 

[Moxioron]

Wanted to get back to you. It worked! Thanks for your help!