Charttiltle as function

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?
 
T

Trond

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.
 
B

Bernie Deitrick

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
 
J

john

see if this helps:

With ActiveSheet.ChartObjects(1).Chart

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

End With
 
P

Peter T

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
 
Top