changing ctart titles using VBA

[colene]

Basically i want multiline chart title in my graph which will change dynamically.
based on the values in cell J10 and J12.
I want the contents to be there in chart

I tried these ways but did not work ...
first try:
mytitle= "=Tabelle1!R22C1"
mysubtitle = mytitle & Chr(10) & "nach MittelEinsatz"
chtChart.ChartTitle.Text = mysubtitle

here i get the value of mytitle as text ...

but when i do the same

chtChart.ChartTitle.Text = "=Tabelle1!R22C1".I get the content of row 22 and column 1 but not a multiline title
second try

I had two cells J10 and J12 named title1 and title2 on Sheet1mytitle = Range("Sheet1!title1") & Chr(10) & Range("Sheet1!title2")
chtChart.ChartTitle.Text = mytitle

I get an error in the line 1.

Can anyone suggest me ..

[Bob Phillips]

This would have been better posted in the Excel forum.

Try this



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$J$10" Or Target.Address = "$J$12" Then

ActiveSheet.ChartObjects(1).Chart.ChartTitle.Text = Me.Range("J10").Value & Chr(10) & Me.Range("J12").Value
End If
End Sub

[colene]

Thanks a lot ..it Works