NatetheGreat
Active Member
- Joined
- Nov 18, 2013
- Messages
- 268
Hello,
All that this code does, is build my multi-series chart and format it.
I then run the following code which will zoom to a specific part of the chart as specified by certain user-defined values in the cells C57 & C58
I then have a button, which brings the chart back to its normal state i.e. all the raw data range it was set to upon creation ( Stats[#all][[RP], Stats[#all][[Cum B], Stats[#all][[Cum A])
Macro assigned to Button
The problem is that simply deleting each of the series (And I have no idea why the macro recorder has put the series deletion all against collection(1) even though they are 3 separate ones...but that's for another day) , and adding them back in again as full series, resets all the formatting that was applied to the chart upon creation. Is there anyway to tell excel via vba to retain the original formatting? Or because that formatting was series specific, upon deletion it is lost forever and does not apply to the new series that replace them?
Alternatively if anyone sees any way to significantly enhance this process, in addition to answering my post question.... then that will be welcomed with open arms.
Forgive me for the long post and ugly code.... I am very new to VBA and am trying to stitch bits of recorded macros, and google searches together to build my worksheet.
Many Thanks All,
Nate
Code:
Sheets("Charts").Select
Range("A20").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Raw Data").Range("Stats[RP]")
'Insert 2nd Chart below 1st Chart, as a line chart with RP as first series
ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
ActiveChart.Parent.Name = "Pos vs Cum B/A"
ActiveChart.ChartTitle.Text = "Pos vs Cum B/A"
'Name the chart, title the chart and position title header
With ActiveChart.Parent
.Top = Sheets("Charts").Cells(25, 1).Top
.Left = Sheets("Charts").Cells(1, 1).Left
.Height = Sheets("Charts").Range("A15:A42").Height
.Width = Sheets("Charts").Range("A42:q42").Width
'Size chart appropriately
ActiveChart.SeriesCollection.Add _
Source:=Worksheets("Raw Data").Range("Stats[[#All],[Cum A]]")
'Add Cum A (Will Go in default as a line series (series2) )
ActiveChart.SeriesCollection.Add _
Source:=Worksheets("Raw Data").Range("Stats[[#All],[Cum B]]")
'Add Cum Bid (Will Go in default as a line series as well (series3) )
ActiveSheet.ChartObjects("Pos vs Cum B/A").Activate
ActiveChart.Legend.Select
Selection.delete
'Get Rid of Unnecessary legend from 2nd Chart
End With
ActiveSheet.ChartObjects("Pos vs Cum B/A").Activate
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(3).Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
'changes Cum B (series3) to column Chart and colour to blue
ActiveChart.SeriesCollection(2).Select
ActiveSheet.ChartObjects("Pos vs Cum B/A").Activate
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
'Change Cum A (series2) to column Chart and color to red
ActiveChart.SeriesCollection(1).Format.Line.Weight = 1.5
ActiveChart.SeriesCollection(1).Border.Color = RGB(255, 255, 0)
ActiveChart.PlotArea.Format.Fill.Transparency = 0.8
' Changes line thickness and transparency and color
ActiveSheet.ChartObjects("Pos vs Cum B/A").Activate
ActiveChart.SeriesCollection(1).Name = "=""RP"""
ActiveChart.SeriesCollection(2).Name = "=""Cum A"""
ActiveChart.SeriesCollection(3).Name = "=""Cum B"""
'Change labels of Series on RollPos CO/Chart
All that this code does, is build my multi-series chart and format it.
I then run the following code which will zoom to a specific part of the chart as specified by certain user-defined values in the cells C57 & C58
Code:
Sub SecndChartResize()
Sheets("charts").Activate
With ActiveSheet.ChartObjects("Position vs Cumiliative Bid/Offer").chart
.SetSourceData Source:=Sheets("Raw Data").Range("w" & Sheets("Charts").Range("c57").Value + 15 & _
":w" & Sheets("charts").Range("c58").Value + 15 & _
",x" & Sheets("charts").Range("c57").Value + 15 & _
":x" & Sheets("charts").Range("c58").Value + 15 & _
",y" & Sheets("charts").Range("c57").Value + 15 & _
":y" & Sheets("charts").Range("c58").Value + 15)
End With
End Sub
I then have a button, which brings the chart back to its normal state i.e. all the raw data range it was set to upon creation ( Stats[#all][[RP], Stats[#all][[Cum B], Stats[#all][[Cum A])
Macro assigned to Button
Code:
Sub testsecondresizeredo()
Sheets("charts").Activate
ActiveSheet.ChartObjects("Pos vs Cum B/A").Activate
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(1).delete
ActiveChart.SeriesCollection(1).delete
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).delete
ActiveSheet.ChartObjects("Position vs Cumiliative Bid/Offer").Activate
ActiveChart.SetSourceData Source:=Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SeriesCollection.Add _
Source:=Worksheets("Raw Data").Range("Stats[[#All],[Cum A]]")
'Add Cumiliative Ask (Will Go in default as a line series (series2) )
ActiveChart.SeriesCollection.Add _
Source:=Worksheets("Raw Data").Range("Stats[[#All],[Cum B]]")
End Sub
The problem is that simply deleting each of the series (And I have no idea why the macro recorder has put the series deletion all against collection(1) even though they are 3 separate ones...but that's for another day) , and adding them back in again as full series, resets all the formatting that was applied to the chart upon creation. Is there anyway to tell excel via vba to retain the original formatting? Or because that formatting was series specific, upon deletion it is lost forever and does not apply to the new series that replace them?
Alternatively if anyone sees any way to significantly enhance this process, in addition to answering my post question.... then that will be welcomed with open arms.
Forgive me for the long post and ugly code.... I am very new to VBA and am trying to stitch bits of recorded macros, and google searches together to build my worksheet.
Many Thanks All,
Nate