VBA Chart title does not populate

Question

I have a list of data and I need to generate a chart for every two lines and give a chart title associated to the 1st line. Example of the data is:

Example

And so on.

The code I am using to create the charts is:

Sub loopChart()

Dim mychart As Chart
Dim myRange As Range
Dim c As Integer
Dim r As Integer
Dim s As Integer
Dim ttl As String




r = 2
While r <= 10 '1=dataSource1, 4=dataSource2, 7=dataSource3
'set data source for the next chart

With Worksheets("Sheet9")
    Set myRange = .Range(.Cells(r, 2), .Cells(r + 1, 14))

End With
'create chart
Sheets("Chart").Select
    ActiveSheet.Shapes.AddChart.Select

    With ActiveChart
         ttl = Range("A" & r)
        .ChartType = xlLineMarkers 'xlLine
        .SetSourceData Source:=myRange, PlotBy:=xlRows  'sets source data for graph including labels
        .SetElement (msoElementLegendRight) 'including legend
        .HasTitle = True
        'dimentions & location:
        .Parent.Top = 244  'defines the coordinates of the top of the chart
        '.Parent.Left = r * 150  'defines the coordinates for the left side of the chart
        .Parent.Height = 200
        .Parent.Width = 300
        .ChartTitle.Formula = ttl
    End With

r = r + 2
Wend

End Sub

So, the 1st chart that generates should get the title on row 2, next chart should have title of row 4... I always get the Chart title on 1st chart that generates but not on any of the other charts. Can anyone please help me on this?

Jon Peltier · Accepted Answer · 2018-05-23 17:03:37Z

1

Please fix below.

ttl = Range("A" & r)

to

ttl = Worksheets("Sheet9").Range("A" & r).Value

edited May 23, 2018 at 17:03

Jon Peltier

6,1011 gold badge32 silver badges28 bronze badges

answered May 22, 2018 at 4:59

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

J.Doe · Accepted Answer · 2018-05-22 05:19:35Z

I think @Dy.Lee answer will do the trick but to go a bit further :

Try avoiding using .select

Proposed workaround :

Sub loopChart()

Dim Ch As Chart
Dim Sh1 As Worksheet: Set Sh1 = Sheets("Sheet9")
Dim Sh2 As Worksheet: Set Sh2 = Sheets("Chart")
Dim L As Integer

L = 0

For i = 2 To 18 Step 2
    Set Ch = Sh2.Shapes.AddChart.Chart
    Ch.SetSourceData Sh1.Range(Sh1.Cells(i, 2), Sh1.Cells(i + 1, 14))
    Ch.ChartType = xlLineMarkers
    Ch.SetElement (msoElementLegendRight)
    Ch.HasTitle = True
    Ch.Parent.Top = 244
    Ch.Parent.Height = 200
    Ch.Parent.Width = 300
    Ch.Parent.Left = L
    Ch.ChartTitle.Caption = Sh1.Cells(i, 1).Value
    L = L + 300
Next i

End Sub

Your code will create each graph at the same location so you'll see only one (the last one) hence the Ch.Parent.Left line with variable value.

Collectives™ on Stack Overflow

VBA Chart title does not populate

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related