Solved

linking a chart title to a cell by using a formula

Posted on 2014-02-26
8
353 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 49

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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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 49

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

776 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