Creating Multiple Charts from Multiple Table

D

DevPup

New Member
Joined
Mar 10, 2017
Messages
10
Would greatly appreciate any help!

I have 4 tables each containing thirty rows. I am using the first row of each table to build the first chart. I need move down to the second row of each table to build the second chart. I need to do this for all the thirty rows and to change the chart number each time. I just need the .Values to move down for each chart, .Name and .XValues don't change.


Sub ChartBuildingTakeTwo()<o:p></o:p>
'<o:p></o:p>
ActiveSheet.Shapes.AddChart.Select<o:p></o:p>
ActiveChart.ChartType = xlColumnClustered<o:p></o:p>
ActiveSheet.ChartObjects("Chart 31").Activate<o:p></o:p>
ActiveChart.SeriesCollection.NewSeries<o:p></o:p>
ActiveChart.SeriesCollection(1).Name= "=""East"""<o:p></o:p>
ActiveChart.SeriesCollection(1).Values = "='Working File'!$C$134:$N$134"<o:p></o:p>
ActiveChart.SeriesCollection(1).XValues = "='Working File'!$C$2:$N$2"<o:p></o:p>
ActiveChart.SeriesCollection.NewSeries<o:p></o:p>
ActiveChart.SeriesCollection(2).Name = "=""West"""<o:p></o:p>
ActiveChart.SeriesCollection(2).Values = "='Working File'!$C$178:$N$178"<o:p></o:p>
ActiveChart.SeriesCollection(2).XValues = "='Working File'!$C$2:$N$2"<o:p></o:p>
ActiveChart.SeriesCollection.NewSeries<o:p></o:p>
ActiveChart.SeriesCollection(3).Name = "=""North"""<o:p></o:p>
ActiveChart.SeriesCollection(3).Values = "='Working File'!$C$91:$N$91"<o:p></o:p>
ActiveChart.SeriesCollection(3).XValues = "='Working File'!$C$2:$N$2"<o:p></o:p>
ActiveChart.SeriesCollection.NewSeries<o:p></o:p>
ActiveChart.SeriesCollection(4).Name = "=""South"""<o:p></o:p>
ActiveChart.SeriesCollection(4).Values = "='Working File'!$C$222:$N$222"<o:p></o:p>
ActiveChart.SeriesCollection(4).XValues = "='Working File'!$C$2:$N$2"<o:p></o:p>
ActiveSheet.ChartObjects("Chart 31").Activate

Thanks
 
Sort by date Sort by votes
Since you say that the first row of each table contains data, not column labels/headers, I'm going to assume that the tables are not ListObjects. If they were, we could loop through each table using the ListObjects object. Also, I've written the code so that the first 15 charts are placed one below the other, and then the next 15 charts are place in the next "column", one below the other. And, lastly, the charts will start to be placed at cell B2 of the active worksheet. Make any changes, as necessary. If you need help in making any changes, let me know and I'll be happy to make them.

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] CreateCharts()

    [COLOR=darkblue]Dim[/COLOR] wsSource [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] rXValues [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rValues [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] oChart [COLOR=darkblue]As[/COLOR] Chart
    [COLOR=darkblue]Dim[/COLOR] vSeriesNames [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LeftPos [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] StartTopPos [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] TopPos [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Gap [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Area [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] TypeName(ActiveSheet) <> "Worksheet" [COLOR=darkblue]Then[/COLOR]
        MsgBox "No worksheet is active.", vbExclamation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] wsSource = Worksheets("Working File")
    
    [COLOR=darkblue]Set[/COLOR] rXValues = wsSource.Range("C2:N2")
    
    [COLOR=darkblue]With[/COLOR] wsSource
        [COLOR=darkblue]Set[/COLOR] rValues = Union(.Range("C134:N163"), .Range("C178:N207"), .Range("C91:N120"), .Range("C222:N251"))
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    vSeriesNames = Array("East", "West", "North", "South")
    
    LeftPos = Range("B2").Left [COLOR=green]'starting left position of chart[/COLOR]
    
    StartTopPos = Range("B2").Top [COLOR=green]'starting top position of chart[/COLOR]
    [COLOR=darkblue]To[/COLOR]pPos = StartTopPos
    
    Gap = 15 [COLOR=green]'gap between charts[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] Rw = 1 [COLOR=darkblue]To[/COLOR] 30
        [COLOR=darkblue]Set[/COLOR] oChart = ActiveSheet.Shapes.AddChart(XlChartType:=xlColumnClustered, Left:=LeftPos, Top:=TopPos, Width:=360, Height:=216).Chart
        [COLOR=darkblue]With[/COLOR] oChart
            .Parent.Name = "Chart " & Rw
            [COLOR=darkblue]While[/COLOR] .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            [COLOR=darkblue]Wend[/COLOR]
            [COLOR=darkblue]For[/COLOR] Area = 1 To rValues.Areas.Count
                [COLOR=darkblue]With[/COLOR] .SeriesCollection.NewSeries
                    .Name = vSeriesNames(Area - 1)
                    .XValues = rXValues
                    .Values = rValues.Areas(Area).Rows(Rw)
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            [COLOR=darkblue]Next[/COLOR] Area
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]If[/COLOR] Rw = 15 [COLOR=darkblue]Then[/COLOR]
            LeftPos = LeftPos + oChart.Parent.Width + Gap
            TopPos = StartTopPos
        [COLOR=darkblue]Else[/COLOR]
            TopPos = TopPos + oChart.Parent.Height + Gap
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] Rw
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!
 
Upvote 0
Thank you! I apologize I didn't send the full macro but I thought it would more of a simple looping. Here the full macro. Also need chart1 and chart2 on the same row beside each other followed by chart3 and chart4 next row and so on... The .Caption at the end also needs to move down to the next row for chart. Thanks again I apologize for the inconvenience.

Sub ChartBuildingTakeTwo()<o:p></o:p>
'<o:p></o:p>
ActiveSheet.Shapes.AddChart.Select<o:p></o:p>
ActiveChart.ChartType = xlColumnClustered<o:p></o:p>
ActiveSheet.ChartObjects("Chart 31").Activate<o:p></o:p>
ActiveChart.SeriesCollection.NewSeries<o:p></o:p>
ActiveChart.SeriesCollection(1).Name= "=""East"""<o:p></o:p>
ActiveChart.SeriesCollection(1).Values = "='Working File'!$C$134:$N$134"<o:p></o:p>
ActiveChart.SeriesCollection(1).XValues = "='Working File'!$C$2:$N$2"<o:p></o:p>
ActiveChart.SeriesCollection.NewSeries<o:p></o:p>
ActiveChart.SeriesCollection(2).Name = "=""West"""<o:p></o:p>
ActiveChart.SeriesCollection(2).Values = "='Working File'!$C$178:$N$178"<o:p></o:p>
ActiveChart.SeriesCollection(2).XValues = "='Working File'!$C$2:$N$2"<o:p></o:p>
ActiveChart.SeriesCollection.NewSeries<o:p></o:p>
ActiveChart.SeriesCollection(3).Name = "=""North"""<o:p></o:p>
ActiveChart.SeriesCollection(3).Values = "='Working File'!$C$91:$N$91"<o:p></o:p>
ActiveChart.SeriesCollection(3).XValues = "='Working File'!$C$2:$N$2"<o:p></o:p>
ActiveChart.SeriesCollection.NewSeries<o:p></o:p>
ActiveChart.SeriesCollection(4).Name = "=""South"""<o:p></o:p>
ActiveChart.SeriesCollection(4).Values = "='Working File'!$C$222:$N$222"<o:p></o:p>
ActiveChart.SeriesCollection(4).XValues = "='Working File'!$C$2:$N$2"<o:p></o:p>
ActiveSheet.ChartObjects("Chart 31").Activate<o:p></o:p>
ActiveSheet.Shapes("Chart 31").ScaleWidth 1.4927084427,msoFalse, _<o:p></o:p>
msoScaleFromTopLeft<o:p></o:p>
ActiveSheet.ChartObjects("Chart 31").Activate<o:p></o:p>
ActiveSheet.Shapes("Chart31").ScaleHeight 1.2482640712, msoFalse, _<o:p></o:p>
msoScaleFromTopLeft<o:p></o:p>
ActiveSheet.ChartObjects("Chart 31").Activate<o:p></o:p>
ActiveChart.SeriesCollection(1).Select<o:p></o:p>
With Selection.Format.Fill<o:p></o:p>
.Visible =msoTrue<o:p></o:p>
.ForeColor.RGB= RGB(146, 208, 80)<o:p></o:p>
.Transparency= 0<o:p></o:p>
.Solid<o:p></o:p>
End With<o:p></o:p>
Selection.Format.ThreeD.BevelTopInset = 6<o:p></o:p>
Selection.Format.ThreeD.BevelTopDepth = 2<o:p></o:p>
Selection.Format.ThreeD.LightAngle = 20<o:p></o:p>
ActiveChart.SeriesCollection(2).Select<o:p></o:p>
Selection.Format.ThreeD.BevelTopInset = 6<o:p></o:p>
Selection.Format.ThreeD.BevelTopDepth = 2<o:p></o:p>
Selection.Format.ThreeD.LightAngle = 20<o:p></o:p>
WithSelection.Format.Fill<o:p></o:p>
.Visible = msoTrue<o:p></o:p>
.ForeColor.RGB= RGB(146, 208, 80)<o:p></o:p>
.Solid<o:p></o:p>
End With<o:p></o:p>
WithSelection.Format.Fill<o:p></o:p>
.Visible =msoTrue<o:p></o:p>
.ForeColor.ObjectThemeColor = msoThemeColorAccent1<o:p></o:p>
.ForeColor.TintAndShade = 0<o:p></o:p>
.ForeColor.Brightness = 0<o:p></o:p>
.Transparency= 0<o:p></o:p>
.Solid<o:p></o:p>
End With<o:p></o:p>
ActiveChart.SeriesCollection(3).Select<o:p></o:p>
ActiveChart.SeriesCollection(3).ChartType = xlLine<o:p></o:p>
ActiveChart.Axes(xlValue).MajorGridlines.Select<o:p></o:p>
ActiveChart.SeriesCollection(3).Select<o:p></o:p>
WithSelection.Format.Line<o:p></o:p>
.Visible =msoTrue<o:p></o:p>
.ForeColor.ObjectThemeColor = msoThemeColorAccent4<o:p></o:p>
.ForeColor.TintAndShade = 0<o:p></o:p>
.ForeColor.Brightness = 0<o:p></o:p>
End With<o:p></o:p>
WithSelection.Format.Line<o:p></o:p>
.Visible =msoTrue<o:p></o:p>
.ForeColor.ObjectThemeColor = msoThemeColorText1<o:p></o:p>
.ForeColor.TintAndShade = 0<o:p></o:p>
.ForeColor.Brightness = 0<o:p></o:p>
.Transparency= 0<o:p></o:p>
End With<o:p></o:p>
WithSelection.Format.Line<o:p></o:p>
.Visible =msoTrue<o:p></o:p>
.DashStyle = msoLineDash<o:p></o:p>
End With<o:p></o:p>
WithSelection.Format.Line<o:p></o:p>
.Visible =msoTrue<o:p></o:p>
.Weight = 1.75<o:p></o:p>
End With<o:p></o:p>
ActiveChart.SeriesCollection(4).Select<o:p></o:p>
ActiveChart.SeriesCollection(4).AxisGroup = 2<o:p></o:p>
ActiveSheet.ChartObjects("Chart 31").Activate<o:p></o:p>
ActiveChart.SeriesCollection(4).Select<o:p></o:p>
ActiveChart.SeriesCollection(4).ChartType = xlLineMarkers<o:p></o:p>
ActiveChart.SeriesCollection(4).Select<o:p></o:p>
With Selection<o:p></o:p>
.MarkerStyle =-4168<o:p></o:p>
.MarkerSize =5<o:p></o:p>
End With<o:p></o:p>
Selection.MarkerSize = 5<o:p></o:p>
WithSelection.Format.Fill<o:p></o:p>
.Visible =msoTrue<o:p></o:p>
.ForeColor.ObjectThemeColor = msoThemeColorAccent1<o:p></o:p>
.ForeColor.TintAndShade = 0<o:p></o:p>
' .ForeColor.Brightness = 0<o:p></o:p>
.Solid<o:p></o:p>
End With<o:p></o:p>
WithSelection.Format.Fill<o:p></o:p>
.Visible =msoTrue<o:p></o:p>
.ForeColor.RGB= RGB(112, 48, 160)<o:p></o:p>
.Transparency= 0<o:p></o:p>
.Solid<o:p></o:p>
End With<o:p></o:p>
Selection.Format.ThreeD.BevelTopInset = 6<o:p></o:p>
Selection.Format.ThreeD.BevelTopDepth = 2<o:p></o:p>
Selection.Format.ThreeD.LightAngle= 20<o:p></o:p>
WithSelection.Format.Line<o:p></o:p>
.Visible =msoTrue<o:p></o:p>
.Weight = 0.25<o:p></o:p>
End With<o:p></o:p>
Selection.Format.Line.Visible = msoFalse<o:p></o:p>
Selection.MarkerStyle = 1<o:p></o:p>
ActiveChart.SeriesCollection(4).ApplyDataLabels<o:p></o:p>
ActiveChart.SeriesCollection(4).DataLabels.Select<o:p></o:p>
Selection.Position= xlLabelPositionCenter<o:p></o:p>
WithSelection.Format.TextFrame2.TextRange.Font.Fill<o:p></o:p>
.Visible =msoTrue<o:p></o:p>
.ForeColor.ObjectThemeColor = msoThemeColorBackground1<o:p></o:p>
.ForeColor.TintAndShade= 0<o:p></o:p>
.ForeColor.Brightness = 0<o:p></o:p>
.Transparency= 0<o:p></o:p>
.Solid<o:p></o:p>
End With<o:p></o:p>
Selection.Format.TextFrame2.TextRange.Font.Bold = msoTrue<o:p></o:p>
ActiveChart.SeriesCollection(4).DataLabels.Select<o:p></o:p>
WithSelection.Format.Fill<o:p></o:p>
.Visible =msoTrue<o:p></o:p>
.ForeColor.RGB= RGB(112, 48, 160)<o:p></o:p>
.Solid<o:p></o:p>
End With<o:p></o:p>
Selection.Format.ThreeD.BevelTopInset = 6<o:p></o:p>
Selection.Format.ThreeD.BevelTopDepth = 2<o:p></o:p>
Selection.Format.ThreeD.LightAngle = 20<o:p></o:p>
ActiveChart.ChartArea.Select<o:p></o:p>
ActiveChart.SetElement (msoElementChartTitleAboveChart)<o:p></o:p>
ActiveSheet.ChartObjects("Chart 31").Activate<o:p></o:p>
ActiveChart.ChartTitle.Select<o:p></o:p>
Selection.Caption= "='Working File'!R266C2" ‘need range from R266C2 through R295C2 also<o:p></o:p>
ActiveChart.Legend.Select<o:p></o:p>
ActiveChart.Legend.Select<o:p></o:p>
Selection.Position= xlBottom<o:p></o:p>
ActiveChart.ChartArea.Select<o:p></o:p>
ActiveSheet.Shapes("Chart 31").ScaleWidth 1.0166091406,msoFalse, _<o:p></o:p>
msoScaleFromTopLeft<o:p></o:p>
ActiveSheet.Shapes("Chart 31").ScaleHeight 1.1406251823,msoFalse, _<o:p></o:p>
msoScaleFromTopLeft<o:p></o:p>
Range("A306").Select<o:p></o:p>
End Sub<o:p></o:p>
 
Upvote 0
Okay, I've made the necessary changes. You'll notice that a separate procedure is used to format each series within each chart. Also, you'll notice that the data labels for the 4th series of each chart are placed above the data points so that they won't cover and hide the data points. Of course, you could always change it to your original setting, if you so desire.

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] CreateCharts()

    [COLOR=darkblue]Dim[/COLOR] wsSource [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] rCaptions [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rXValues [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rValues [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] oChart [COLOR=darkblue]As[/COLOR] Chart
    [COLOR=darkblue]Dim[/COLOR] oSeries [COLOR=darkblue]As[/COLOR] Series
    [COLOR=darkblue]Dim[/COLOR] vSeriesNames [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ChrtWidth [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ChrtHeight [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] StartLeftPos [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LeftPos [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] TopPos [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Gap [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] RowIndex [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] SeriesIndex [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] ErrHandler
    
    [COLOR=darkblue]If[/COLOR] TypeName(ActiveSheet) <> "Worksheet" [COLOR=darkblue]Then[/COLOR]
        MsgBox "No worksheet is active.", vbExclamation
        [COLOR=darkblue]GoTo[/COLOR] ExitTheSub
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] wsSource = Worksheets("Working File")
    
    [COLOR=darkblue]With[/COLOR] wsSource
        [COLOR=darkblue]Set[/COLOR] rCaptions = .Range("B266:B295")
        [COLOR=darkblue]Set[/COLOR] rXValues = .Range("C2:N2")
        [COLOR=darkblue]Set[/COLOR] rValues = Union(.Range("C134:N163"), .Range("C178:N207"), .Range("C91:N120"), .Range("C222:N251"))
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    vSeriesNames = Array("East", "West", "North", "South")
    
    ChrtWidth = 546
    ChrtHeight = 308
    
    StartLeftPos = Range("B2").Left
    LeftPos = StartLeftPos
    TopPos = Range("B2").[COLOR=darkblue]To[/COLOR]p
    
    Gap = 15
    
    [COLOR=darkblue]For[/COLOR] RowIndex = 1 To 30
        [COLOR=darkblue]Set[/COLOR] oChart = ActiveSheet.Shapes.AddChart(XlChartType:=xlColumnClustered, Left:=LeftPos, [COLOR=darkblue]To[/COLOR]p:=TopPos, Width:=ChrtWidth, Height:=ChrtHeight).Chart
        [COLOR=darkblue]With[/COLOR] oChart
            .Parent.Name = "Chart " & RowIndex
            [COLOR=darkblue]While[/COLOR] .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            [COLOR=darkblue]Wend[/COLOR]
            [COLOR=darkblue]For[/COLOR] SeriesIndex = 1 To rValues.Areas.Count
                [COLOR=darkblue]Set[/COLOR] oSeries = .SeriesCollection.NewSeries
                [COLOR=darkblue]With[/COLOR] oSeries
                    .Name = "=""" & vSeriesNames(SeriesIndex - 1) & """"
                    .XValues = rXValues
                    .Values = rValues.Areas(SeriesIndex).Rows(RowIndex)
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                FormatSeries oSeries, SeriesIndex
            [COLOR=darkblue]Next[/COLOR] SeriesIndex
            .SetElement msoElementChartTitleAboveChart
            .ChartTitle.Caption = "=" & rCaptions.Cells(RowIndex).Address(, , , [COLOR=darkblue]True[/COLOR])
            .Legend.Position = xlLegendPositionBottom
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]If[/COLOR] (RowIndex Mod 2) [COLOR=darkblue]Then[/COLOR]
            LeftPos = LeftPos + ChrtWidth + Gap
        [COLOR=darkblue]Else[/COLOR]
            LeftPos = StartLeftPos
            TopPos = TopPos + ChrtHeight + Gap
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] RowIndex
    
ExitTheSub:
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
ErrHandler:
    MsgBox "Error " & Err.Number & ":  " & Err.Description, vbCritical, "Error"
    [COLOR=darkblue]Resume[/COLOR] ExitTheSub
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] FormatSeries([COLOR=darkblue]ByVal[/COLOR] oSeries [COLOR=darkblue]As[/COLOR] Series, [COLOR=darkblue]ByVal[/COLOR] SeriesIndex [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR])
    [COLOR=darkblue]With[/COLOR] oSeries
            [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] SeriesIndex
                [COLOR=darkblue]Case[/COLOR] 1
                    [COLOR=darkblue]With[/COLOR] .Format
                        [COLOR=darkblue]With[/COLOR] .Fill
                            .Visible = msoTrue
                            .ForeColor.RGB = RGB(146, 208, 80)
                            .Transparency = 0
                            .Solid
                        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                        [COLOR=darkblue]With[/COLOR] .ThreeD
                            .BevelTopInset = 6
                            .BevelTopDepth = 2
                            .LightAngle = 20
                        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                [COLOR=darkblue]Case[/COLOR] 2
                    [COLOR=darkblue]With[/COLOR] .Format
                        [COLOR=darkblue]With[/COLOR] .Fill
                            .Visible = msoTrue
                            .ForeColor.ObjectThemeColor = msoThemeColorAccent1
                            .ForeColor.TintAndShade = 0
                            .ForeColor.Brightness = 0
                            .Transparency = 0
                            .Solid
                        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                        [COLOR=darkblue]With[/COLOR] .ThreeD
                            .BevelTopInset = 6
                            .BevelTopDepth = 2
                            .LightAngle = 20
                        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                [COLOR=darkblue]Case[/COLOR] 3
                    .ChartType = xlLine
                    [COLOR=darkblue]With[/COLOR] .Format.Line
                        .Visible = msoTrue
                        .ForeColor.ObjectThemeColor = msoThemeColorText1
                        .ForeColor.TintAndShade = 0
                        .ForeColor.Brightness = 0
                        .Transparency = 0
                        .DashStyle = msoLineDash
                        .Weight = 1.75
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                [COLOR=darkblue]Case[/COLOR] 4
                    .AxisGroup = 2
                    .ChartType = xlLineMarkers
                    .MarkerStyle = 1
                    .MarkerSize = 5
                    [COLOR=darkblue]With[/COLOR] .Format
                        [COLOR=darkblue]With[/COLOR] .Fill
                            .Visible = msoTrue
                            .ForeColor.RGB = RGB(112, 48, 160)
                            .Transparency = 0
                            .Solid
                        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                        [COLOR=darkblue]With[/COLOR] .Line
                            .Visible = msoTrue
                            .Weight = 0.25
                        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                        [COLOR=darkblue]With[/COLOR] .ThreeD
                            .BevelTopInset = 6
                            .BevelTopDepth = 2
                            .LightAngle = 20
                        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                    .ApplyDataLabels
                    [COLOR=darkblue]With[/COLOR] .DataLabels
                        .Position = xlLabelPositionAbove
                        [COLOR=darkblue]With[/COLOR] .Format.TextFrame2.TextRange.Font
                            [COLOR=darkblue]With[/COLOR] .Fill
                                .Visible = msoTrue
                                .ForeColor.ObjectThemeColor = msoThemeColorBackground1
                                .ForeColor.TintAndShade = 0
                                .ForeColor.Brightness = 0
                                .Transparency = 0
                                .Solid
                            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                            .Bold = msoTrue
                        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                        [COLOR=darkblue]With[/COLOR] .Format
                            [COLOR=darkblue]With[/COLOR] .Fill
                                .Visible = msoTrue
                                .ForeColor.RGB = RGB(112, 48, 160)
                                .Solid
                            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                            [COLOR=darkblue]With[/COLOR] .ThreeD
                                .BevelTopInset = 6
                                .BevelTopDepth = 2
                                .LightAngle = 20
                            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Chart Series title with multiple cells
Replies
4
Views
300
mikejvir
M
W
  • Question Question
Create a Dynamic Line Chart for last 4 weeks of data
Replies
2
Views
298
StephenCrump
S
P
  • Question Question
Hoping to get help with a Macro!
Replies
0
Views
327
PEM000
P
L
  • Locked
  • Question Question
How set range for chart in powerpoint created from vba
Replies
0
Views
218
Legacy 519704
L
R
  • Question Question
Could I ask for VBA (or python) code to create a new workbooks?
Replies
2
Views
164
Radoslaw Poprawski
R

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top