Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

linking a chart title to a cell by using a formula

Posted on 2014-02-26
8
Medium Priority
?
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 53

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 1000 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 53

Accepted Solution

by:
Rgonzo1971 earned 1000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

609 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