Create Chart Titles from named ranges

Dear Experts:
I got numerous charts with the following user-defined names: MyChart_01, MyChart_02 … MyChart_15 etc. (less than 100 charts). All of them do not have any chart titles.

The cells where the chart titles reside are all named: ChartTitle_01, ChartTitle_02, ChartTitle_03. The suffix numbers correspond with the respective suffix numbers of the chart

Based on the above facts, I would like to automatically add chart titles using VBA ...

i.e. MyChart_01 will get the chart title from the named cell 'ChartTitle_01',
'MyChart_02' will get the chart title from the named cell 'ChartTitle_02' etc.

The named cell ranges of the chart titles are all in one column and in sequential order.
I wonder whether the matching up of chart titles and charts would also be possible if the chart titles would not be in any logical order?

Help is much appreciated. Thank you very much in advance.

I have attached a sample file for your convenience.

Regards, Andreas  ChartTitle-Create-For-All-Charts.xlsm
Andreas HermleTeam leaderAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
For C = 1 To 15

Ch = Application.WorksheetFunction.Text(C, "00")

ChSel = "MyChart_" & Ch
ChTitle = Range("ChartTitle_" & Ch)

ActiveSheet.ChartObjects(ChSel).Activate
    ActiveChart.ChartArea.Select
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = ChTitle
    End With

Next C

Open in new window

0
 
Rob HensonFinance AnalystCommented:
Try this:

For Ch = 1 to 15

ChSel = "MyChart_"& text(Ch,"00")
ChTitle = "ChartTitle_" & Range("ChartTitle_" & text(Ch,"00")

ActiveSheet.ChartObjects(ChSel).Activate
    ActiveChart.ChartArea.Select
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = ChTitle
    End With

Next Ch

Open in new window


Cheers
Rob H
0
 
Rob HensonFinance AnalystCommented:
Ooops, need another bracket at the end of Line 4.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Rob HensonFinance AnalystCommented:
Just tested, VBA doesn't recognise the Text function. Need to get round that.

Also line 4 doesn't need the "ChartTitle_" at the start as that adds that text to the contents of the named range.
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi,

will test it and give you feedback as soon as possible. Many thanks so far.
regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi rob,
great job! Works just fine. Thank you very much.

There is one thing I would like you to look into. I'd rather not have to hard-code the number of loops the macro has to run. The number of loops is determined by the number of named ranges with the following make-up: ChartTitle_00

I am referring to:

For intCount = ActiveWorkbook.Names.Count To 1 Step -1
        Set nme = ActiveWorkbook.Names(intCount)
        If nme.Name Like "ChartTitle_##" Then
            i = i + 1
        End If
Next
    MsgBox i & " ChartTitle_xx names found!!"
End If

SO, how has your code to be re-written to accommodate this new requirement.

Help is much appreciated.Thank you very much in advance.

Regards, Andreas

0
 
Rob HensonFinance AnalystCommented:
You could set an area where you run the paste list of named ranges into a list and then have a countif formula for those that start "ChartTitle".

Selection.ListNames

Open in new window


Will put a list of named ranges into the current cell. You would then need a dynamic range to determine the range to count for the chart titles. The result of the count can then be fed into the routine automatically and into the message box to confirm how many graphs were amended.

Cheers
Rob H
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Rob,

I just integrated my code snippet into your code and it works just fine.

Thank  you very much for your great and professional help.

Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Rob,

great job. thank you very much.

Regards, Andreas
0
 
Rob HensonFinance AnalystCommented:
Glad to be of assistance.

Cheers
Rob
0
All Courses

From novice to tech pro — start learning today.