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?

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?

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?

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" <(E-Mail Removed)> wrote in message
news:7DAEA858-4598-4D74-9696-(E-Mail Removed)...
> 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?

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?

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" <(E-Mail Removed)> wrote in message
newsFC0D7DF-B92F-4853-90C1-(E-Mail Removed)...
>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?

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?

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?