Solved

linking a chart title to a cell by using a formula

Posted on 2014-02-26
8
350 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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,…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

948 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now