PDA

View Full Version : "For Loop" to Change "Chart title" based on sequential cells



RBradbury
03-23-2017, 01:34 AM
Dear all,

What a great forum, thank you for taking the time to help this simple problem.

I have create 'Radar Charts' to represent individual student attainment and I wish the chart title to reflect the students name.

I have managed to find the syntax to do this for the first instance, but cannot figure out the loop code to make it do it to all available charts (this will be a variable due to class sizes) and to move to the next cell (next student name): (Please forgive poor naming of macro)




Sub Macro5()
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Text = "=Radar!A2"
End With

End Sub


I have uploaded the file with 2 sheets:

1. Radar
2. Yr 7 Data

Sheet 2 'Yr 7 Data', will have the class register where I wish the Charts Titles will obtain their new Titles, based off of the correlating student.

mana
03-23-2017, 02:54 AM
Option Explicit

Sub test()
Dim cho As ChartObject
Dim s As String

For Each cho In ActiveSheet.ChartObjects
With cho.Chart
s = Split(.SeriesCollection(1).Formula, ",")(1)
.HasTitle = True
.ChartTitle.Text = "=" & Range(s)(1, 0).Address(external:=True)
End With
Next

End Sub

RBradbury
03-23-2017, 03:50 AM
Hello Mana,

It works perfectly. Thank you very much for your incredibly speedy and accurate response.

As i am brand new to all this, could you let me know what I need to research to understand the separate parts, such as:

Dim
String
Also - How '.ChartTitle.Text = "=" & Range(s)(1, 0).Address(external:=True)' means, read from the second sheet, I don't quite understand.
If you don't have time, I completely understand.

Thank you very much.

mana
03-23-2017, 04:23 AM
Sorry, I am japanese.
I can't explain well in my poor English.

RBradbury
03-23-2017, 04:33 AM
No problem. Thank you for your time.
Have a great day. :)