Solved

linking a chart title to a cell by using a formula

Posted on 2014-02-26
8
354 Views
Last Modified: 2014-03-02
Dear Experts:

I am trying to link a chart title to a cell (D1) by using a formula. It somehow does not work. Running below code I only get the default entry 'Chart Title' (see line 16)

If I integrate 'Line 13' into the code everything is running fine.

Although the code works when run with line 13, I still would like to be able to link the chart title to a cell using a formula.

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

Regards, Andreas


Sub AddTitles_MyChrtDeps01()
Dim shp As Shape
Dim ChartTitleText01 As String


ChartTitleText01 = Sheets("Results").Range("D1")


With Worksheets("Results").ChartObjects("MyChrt01").Chart
    .HasTitle = True
   
   ' Below line is working
   ' .ChartTitle.Text = ChartTitleText01

    ' Chart Title Formula line not working!
    .ChartTitle.Formula = "='Results'!$D$1"

    .ChartTitle.Format.TextFrame2.TextRange.Font.Name = "Verdana"
    .ChartTitle.Format.TextFrame2.TextRange.Font.Size = 11
    .ChartTitle.Format.TextFrame2.TextRange.Font.Bold = True
    .ChartTitle.IncludeInLayout = True
End With

End Sub

Open in new window

0
Comment
Question by:AndreasHermle
  • 3
  • 3
  • 2
8 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39888976
Hi,

The code seems fine

What error do you get?

Regards
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39888977
Hi,

Instead of this statement:

.ChartTitle.Formula = "='Results'!$D$1"

Does the ChartTitle show the expected text if you change the statement to read:

.ChartTitle.Text = "='Results'!$D$1"

?

BFN,

fp.
0
 

Author Comment

by:AndreasHermle
ID: 39889105
Dear both,

Rgonzo1971, ok my code may works but the contents of  the chart title is a static text
(cannot see any formula in the formula bar) instead of a dynamic text that should change if the contents of D1 changes.

And now this is really weird. If I move line 18 to line 21 (the formatting part) in front of line 16 everything is fine, i.e.

.ChartTitle.Formula = "='Results'!$D$1"

is the last line.

Nevertheless thank you very much for your swift help. I really appreciate it.

Regards, Andreas
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 250 total points
ID: 39889149
Using .ChartTitle.Text = "='Results'!$D$1" should make the title dynamic; if cell [D1] changes value, then the ChartTitle will also change to match.
0
 

Author Comment

by:AndreasHermle
ID: 39891317
okay, I will give a try and then let you know.

Thank you very much fanpages.

Regards, andreas
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 39891462
Hi,

it seems that as soon there is a modification of the TextRange Properties the Formula is overwritten with the Text Result of the formula

It means that you could use the ChartTitle.Font Properties directly, then the formula won't be replaced
Dim shp As Shape
Dim ChartTitleText01 As String


ChartTitleText01 = Sheets("Results").Range("D1")


With Worksheets("Results").ChartObjects("myChrt01").Chart
    
    .HasTitle = True
   
   ' Below line is working
   ' .ChartTitle.Text = ChartTitleText01

    ' Chart Title Formula line not working!
    .ChartTitle.Formula = "='Results'!$D$1"

    .ChartTitle.Font.Name = "Verdana"
    .ChartTitle.Font.Size = 11
    .ChartTitle.Font.Bold = True
    .ChartTitle.IncludeInLayout = True
    
End With

End Sub

Open in new window

Regards
0
 

Author Closing Comment

by:AndreasHermle
ID: 39898774
Dear both,

thank you very much for your valuable help. I really appreciate it

Regards, Andreas
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39899122
You're very welcome Andreas.

Good luck with your project.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

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