Charttiltle as function

  • Thread starter Thread starter Trond
  • Start date Start date
T

Trond

I have programmed a macro that makes a pivottable and a pivotdiagram. In this
diagram I have a charttitle that I want to be equal to the content of one
cell (D2).
So if the content of the cell changes, I want the Charttitle also to change.

Manually I can do this by selecting the charttitle and use the function
(=D2) on the top of the excel-sheet. But this will not work when I try the
recorder.

Can anyone help?
 
No. I produce the table and diagram once. As I change the pagefield in the
pivot, I also want the title to change according to it.
 
Trond,

You need to activate the chart and set the link as an R1C1 style formula in Text

ActiveSheet.ChartObjects("Chart 3").Activate 'or other code, depending on.....
ActiveChart.ChartTitle.Text = "=Sheet1!R2C4"


HTH,
Bernie
MS Excel MVP
 
see if this helps:

With ActiveSheet.ChartObjects(1).Chart

.HasTitle = True
.ChartTitle.Text = Range("D2").Value

End With
 
I'm not sure if your really mean a ChartTitle (on a diagram?) but if you do
try something like this

Sub test()
Dim sLink As String
Dim rCell As Range
Dim cht As Chart
Dim ct As ChartTitle

Set rCell = ActiveSheet.Range("D2")
sLink = rCell.Address(, , xlR1C1, True)

Set cht = ActiveSheet.ChartObjects(1).Chart
Set ct = cht.ChartTitle

ct.Text = "=" & sLink

End Sub

Regards,
Peter T
 
Back
Top