asked on

linking a chart title to a cell by using a formula

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

Rgonzo1971

Hi,

The code seems fine

What error do you get?

Regards

[ fanpages ]

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.

Andreas Hermle

ASKER

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

SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreas Hermle

ASKER

okay, I will give a try and then let you know.

Thank you very much fanpages.

Regards, andreas

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership