Deleting and Adding new Chart Using VBA

[rodell]

I am working on a product to generate a monthly status update. I have to include this in a monthly report so what I am trying to do is delete the last chart and replace it with a new chart when the end of the month rolls around. I am fairly new to VBA so I don't have all the detailed indepth knowledge as you might see from my code. I have been able to delete the prior months chart but cannot get it to insert the new chart in the format I had for the deleted one. I recorded my macro initially and saved the chart as a template and tried to code it to grab the template and update my data. When I saved it, I saved as was (Chart 1) and assumed this would be what I needed to call it when updating my old data with the new chart. I keep getting Run time Error 91 When attempting to Apply Chart Template. I cannot figure it out and I don't understand most of what others are saying on google, especially when I recorded the macro the first time and it never added any code similar to what others are saying it needs. Here is my code if anyone can look at it and tell me where I am going wrong.

Sub CreateGraph2()
'
' CreateGraph2 Macro
'
'
Application.ScreenUpdating = False
On Error Resume Next
ActiveSheet.ChartObjects.Delete
On Error GoTo 0

Range("D11:K15").Select
ActiveChart.ApplyChartTemplate ("A:\Governance\Compliance\ASB Documents\Chart1.crtx")
'ActiveSheet.Shapes.AddChart.Select
'ActiveChart.ChartType = xlCylinderColStacked100
ActiveChart.SetSourceData Source:=Range("'Update Browser Stats'!$D$11:$K$15")
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart1.").IncrementLeft -181.5
ActiveSheet.Shapes("Chart1.").IncrementTop 42.75
ActiveSheet.Shapes("Chart1.").ScaleWidth 1.4479166667, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart1.").ScaleHeight 1.375, msoFalse, msoScaleFromTopLeft

ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "IE 11 Compliance - Post ER 17"
Selection.Format.TextFrame2.TextRange.Characters.Text = _
"IE 11 Compliance - Post ER 17"
With Selection.Format.TextFrame2.TextRange.Characters(1, 29).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 2).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
With Selection.Format.TextFrame2.TextRange.Characters(3, 27).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).ApplyDataLabels

End Sub

[offthelip]

Can I ask why you are deleting a chart when you immediately want to replace it with an identical one with new data, why not just change the data

with something like;


ActiveSheet.Shapes("Chart1.").SetSourceData Source:=Range("'Update Browser Stats'!$D$11:$K$15")
ActiveSheet.ChartObjects("chart 1").Chart.SeriesCollection(1).Values = Range("'Update Browser Stats'!$D$11:$D$15")
Note I have not compiled or tested this so it probably has errors in it.

[rodell]

That's a good question and I suppose I can do that. My only question would be that I am automating a process that gets the data quicker and creates a graph so I can insert it into a report and forward it to my boss upon request. So I have created a command button (Create Graph Button) that Creates the graph based off data located on another sheet within the workbook. I normally don't have to submit this report except on a monthly basis. At which time, as the data is compiled on a weekly basis on another worksheet, I normally don't update the chart data until about every 4 or 5 weeks. My idea was to just hit the create graph button that would delete the last one created and automatically create a new one based on the data that had compiled since that last report. However, I like your suggestion, but would have to ask how I should go about this. Should I go ahead and just create my chart as to how I want it to appear and then perhaps change my create graph button to an "Update Chart Data" button and then just create a macro for the button with the code you suggested?



Can I ask why you are deleting a chart when you immediately want to replace it with an identical one with new data, why not just change the data

with something like;


ActiveSheet.Shapes("Chart1.").SetSourceData Source:=Range("'Update Browser Stats'!$D$11:$K$15")
ActiveSheet.ChartObjects("chart 1").Chart.SeriesCollection(1).Values = Range("'Update Browser Stats'!$D$11:$D$15")
Note I have not compiled or tested this so it probably has errors in it.

[offthelip]

Your suggestion of how to do it is the way I would do it, because you can format the graph as you want it and then you only need to change the data.
here is some code that I have used to do a very similar thing. The graph is "chart 1" which exists on the sheet, the values plotted are controlled by the number in
A2, B2 and C2. A2 is the start row number, B2 is the start column number for the three columns that are plotted, and C2 is the number of points to plot.

Sub newchart()


Worksheets("Sheet1").Select
Row = Cells(2, 1)
col = Cells(2, 2)
Offset = Cells(2, 3)


Worksheets("Sheet1").ChartObjects("chart 1").Chart.SetSourceData Source:=Sheets("sheet1").Range(Cells(Row, col), Cells(Row + Offset, col + 3)), PlotBy:=xlColumns
Worksheets("Sheet1").ChartObjects("chart 1").Chart.ChartType = xlLine
Worksheets("Sheet1").ChartObjects("chart 1").Chart.SeriesCollection(1).Name = "Test Name"
Worksheets("Sheet1").ChartObjects("chart 1").Activate


End Sub

[rodell]

Awesome. Thanks for your help. I actually did get it to work after a little tweaking (mainly the ChartType). However, when it updates it changes my format. It puts my legend information on the X axis and my dates are now over in the legend area (right side of page). I tried recording a macro for selecting data and switching columns but it only records the following:

ActiveChart.Walls.Select
ActiveChart.SetSourceData

It actually does not record the switch column process, so I am not quite sure how to incorporate this automated process into my code? I guess I could manually do it everytime, but it would be nice for it to automatically update with the way I originally set up the chart

[offthelip]

Hi
By the sound of it you need to specify the X and y data values which you can do by adding statements like this to your "update chart" macro:
set X values

Worksheets("Sheet1").ChartObjects("chart 1").Chart.SeriesCollection(1).XValues = Sheets("Sheet 1").Range(Cells(row, col, Cells(row+offset, col))

you can also set the Values with a similar statement


Worksheets("Sheet1").ChartObjects("chart 1").Chart.SeriesCollection(1).Values = Sheets("Sheet 1").Range(Cells(row, col+1, Cells(row+offset, col+2))


Note the ranges I have specified are just for example, since I don't know how your data is laid out.

[p45cal]

When you update a chart with .SetSourceData if you have multiple series which you've moved around, yes, it will probably undo all that for you!
There is another way to do this; by updating each series one at a time. Record yourself a macro of you changing the source data one series at a time. Do this after you have the chart as you want it and just want to change the where the data for each series comes from. You might get something like this:
ActiveChart.SeriesCollection(1).Values = "=Sheet2!$A$2:$A$7"
ActiveChart.SeriesCollection(2).Values = "=Sheet2!$B$2:$B$7"
That way Excel won't try to re-arrange stuff while it builds a chart from scratch when .SetsourceData is used.
If the recorded code is too difficult for you to alter/tweak, post it here.

edit post posting: and offthelip beat me to it.

[rodell]

p45cal - thanks for the suggestion, unfortunately I cannot get yours to work, I get all sorts of errors and Ive tried to tweak it every which way I know and nothing but errors, I can still get chart to update, but it changes my format still

[rodell]

offthelip, not sure what I am doing wrong and maybe its just the fact that Im a newbie, but I cant get either one of the suggestions to work properly. Seems like the first one that pertains to the Xvalues should be what is needed to fix it, but I still keep getting errors such as Expected:list separator or). I have pasted my code as to where I am at, at this point. I can get the graph to update, just not formatted properly afterwards. I still have to select data and switch columns. I am pretty sure its because I have not put the Range data in properly, but wasn't quite sure how that should be put in. I also put a copy of the data that I am using to update my chart. 6/17 is in cell E11 on my worksheet, 6/29 is in cell k11 on my worksheet, and this is what I need to remain on my X axis, however when I run my macro, all my dates are on the right hand side where the legend is and the legend entries are now on my X axis.




6/17
6/17
6/23
6/23
6/27
6/27
6/29


Primary Dealers
24.07%
24.07%
24.07%
24.07%
24.07%
24.07%
24.07%


Bidder
39.95%
39.95%
39.95%
39.95%
39.95%
39.95%
39.95%


Operations*
19.58%
19.58%
19.58%
19.58%
19.58%
19.58%
19.58%


Other**
6.61%
6.61%
6.61%
6.61%
6.61%
6.61%
6.61%


Total
90.21%
90.21%
90.21%
90.21%
90.21%
90.21%
90.21%




Worksheets("Update Browser Stats").ChartObjects("Chart 6").Chart.SetSourceData Source:=Sheets("Update Browser Stats").Range("'Update Browser Stats'!$D$11:$K$15"), PlotBy:=xlColumns
Worksheets("Update Browser Stats").ChartObjects("Chart 6").Chart.ChartType = xlCylinderColStacked100
Worksheets("Update Browser Stats").ChartObjects("chart 6").Chart.SeriesCollection(1).XValues = Sheets("Update Browser Stats").Range(E11(11, E, Cells(11+offset, E))
Worksheets("Update Browser Stats").ChartObjects("Chart 6").Activate

[offthelip]

I a quick glance this looks wrong,

Worksheets("Update Browser Stats").ChartObjects("chart 6").Chart.SeriesCollection(1).XValues = Sheets("Update Browser Stats").Range(E11(11, E, Cells(11+offset, E))


try:
Worksheets("Update Browser Stats").ChartObjects("chart 6").Chart.SeriesCollection(1).XValues = Sheets("Update Browser Stats").Range( Cells(11,5),cells(11,11))

The number referencing is rows then columns so the date range which is in E11 to K11 is row 11 columns 5 to 11. There is a very useful excel function that I use all the time "=column()" which tells you what the column number is. I prefer numbers in VBa because it is easier to deal with indexing.

Also I think you need to change the source data reference to '!$E$12:$K$15", this will get rid of the legends, you can then put the legends in with VBA

Worksheets("Update Browser Stats").ChartObjects("Chart 6").Chart.SetSourceData Source:=Sheets("Update Browser Stats").Range("'Update Browser Stats'!$E$12:$K$15"), PlotBy:=xlColumns

Worksheets("Update Browser Stats").ChartObjects("chart 1").Chart.SeriesCollection(1).Name = "put a name in here"

I just had a further thought, you might find it best to set the data as four different series, all with the same Xvalues, but you can then set the data and name each series separately .
just change the index in chart.seriescollection(1) to 2,3 and 4 sucessively.

[rodell]

I would like to say that worked, but it didn't. It did update graph, but it did not keep the xvalues where I needed them and when I went to select data, and switch columns, the option to switch columns was grayed out and would not allow me to select it. So I had to comment it out, rerun the macro and then switch columns.

[offthelip]

I rather suspect that the switch columns functionality is not available in VBA, I have certainly never used it. Since it is functionality which is changing the automatic options which excel defaults to, I also suspect that programming in the X and Y axes as we are doing will always switch off the "default" control which is the switch columns. What this means is that my suggestion of just changing the data will never work fully because you want to use the "switch columns " Functionality. Unfortunately I think you are going run into similar problems trying to create a graph from scratch.
So what about not changing the graph at all, but moving the data around instead, does the number of columns with dates in it change alot from month to month?
Does the number of rows change alot?
Another alternative is to plot two graphs one in each direction, then you could use my method.

[p45cal]

We're working in a vacuum here; it's impossible to guess your setup. Provide a few files: an xlsm file (washed/sanitized if there's sensitive data), the Chart1.crtx file, and tell us what sort of changes occur to the data you want to plot (perhaps an extra sheet being a copy of before/after update of data).
That way, I can (almost) guarantee a successful solution.

[rodell]

offthelip and p45cal, I have not had the chance to try your suggestions yet. Other issues with higher priorities came up and Ive been working those issues. I am messing around with this on downtime when possible. I am still working on those issues today and may not get the chance to go back and look at this until early next week. I truly appreciate the assistance from both of you and will probably be posting some questions again next week depending on whether or not I can get this to work. Once again thanks and enjoy your 4th of July.