Charttiltle as function
 

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?

Charttiltle as function
 

Are you running the macro each time the chart title changes?

Barb Reinhardt



"Trond" wrote:

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?

Charttiltle as function
 

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.



"Barb Reinhardt" wrote:

Are you running the macro each time the chart title changes?

Barb Reinhardt



"Trond" wrote:

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?

Charttiltle as function
 

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


"Trond" wrote in message
...
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.



"Barb Reinhardt" wrote:

Are you running the macro each time the chart title changes?

Barb Reinhardt



"Trond" wrote:

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?

Charttiltle as function
 

see if this helps:

With ActiveSheet.ChartObjects(1).Chart

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

End With
--
jb


"Trond" wrote:

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?

Charttiltle as function
 

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

"Trond" wrote in message
...
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?

Charttiltle as function
 

But this won't update when I change the content of D2???


"john" wrote:

see if this helps:

With ActiveSheet.ChartObjects(1).Chart

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

End With
--
jb


"Trond" wrote:

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?

Charttiltle as function
 

Try Peter's suggestion.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Trond wrote:
But this won't update when I change the content of D2???


"john" wrote:

see if this helps:

With ActiveSheet.ChartObjects(1).Chart

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

End With
--
jb


"Trond" wrote:

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?