?
Solved

Linking chart titles to cell values (named ranges) using a VBA loop

Posted on 2011-05-03
5
Medium Priority
?
259 Views
Last Modified: 2012-05-11
Dear Experts:

Below macro adds a DYNAMIC chart title to the first chart using a NAMED cell range. The named range in the below code example is 'ChartTitle_01' (workbook-level name)

I would like to get below code changed to accomodate the following requirements:

All Charts in my workbook are named as follows: Chart_01, Chart_02, Chart_03 etc.
The chart titles are all named ranges as follows: ChartTitle_01, ChartTitle_02, ChartTitle_03 etc.

So the macro should loop thru all the charts and add Chart Titles using the named range.

Could someone please help me with this?

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

Regards, Andreas

I have attached a sample file for your convenience.

 LinkChartTitle-NamedRange.xlsm
Sub LinkChartTitle_NamedRange()

With Worksheets("PieCharts").ChartObjects(1).Chart
    .HasTitle = True
    .ChartTitle.Text = "=PieCharts!ChartTitle_01" 
End With
End Sub
End Sub

Open in new window

0
Comment
Question by:AndreasHermle
  • 3
5 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 35512050
Hello Andreas,

maybe like this:

Sub LinkChartTitle_NamedRange()
Dim cht As ChartObject
Dim i As Integer
i = 1
For Each cht In Worksheets("PieCharts").ChartObjects
    cht.Chart.HasTitle = True
    cht.Chart.ChartTitle.Text = Range("PieCharts!ChartTitle_" & Format(i, "00"))
    i = i + 1
Next
End Sub

Open in new window


cheers, teylyn
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 35512190
Andreas,

Was there something wrong with the original question and solution???

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26985247.html

Is this to further investigate not having to hard code the number of charts?

Cheers
Rob H
0
 

Author Comment

by:AndreasHermle
ID: 35514238
Hi Rob and teylyn:

not at all. Everything was ok with your code.  It runs just fine!

My new objective is to get a code where the chart title updates on cell changes. This is the case with the BELOW code.

Now, teylyn, first of all, thank you very much for your swift reply. The code is working, but, regrettably, the chart tiles do not reflect changes in the linked cell references.

Please have a look at the attached picture. After running below code on my sample chart file, the first chart title is linked to the named range 'ChartTitle_01'. That is, clicking on the first chart title shows the following entry in the formula bar:

=LinkChartTitle_NamedRange.xlsm!ChartTitle_01

I would like apply this linking to all chart titles? Is this feasible?

Sub LinkChartTitle_NamedRange()

With Worksheets("PieCharts").ChartObjects(1).Chart
    .HasTitle = True
    .ChartTitle.Text = "=PieCharts!ChartTitle_01"
End With
End Sub
 Linked_ChartTitle
0
 

Author Comment

by:AndreasHermle
ID: 35514259
Dear both:

forget what I have just posted. I just slightly altered teylyn's code and it works just fine.

So, problem SOLVED!

Please have a look at the slightly altered code below.

Regards, Andreas
Sub LinkChartTitle_NamedRange_SlightlyAltered()
Dim cht As ChartObject
Dim i As Integer
i = 1
For Each cht In Worksheets("PieCharts").ChartObjects
    cht.Chart.HasTitle = True
    cht.Chart.ChartTitle.Text = "=PieCharts!ChartTitle_" & Format(i, "00")
    i = i + 1
Next
End Sub

Open in new window

0
 

Author Closing Comment

by:AndreasHermle
ID: 35514744
Thank you very much for your professional help.
Regards, Andreas
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question