Macro Program for Charts Updates Titles.Removes Title formula linked to cell ref

[ianb]

Hi,


I have this program that will change charts on each of my worksheets.

Some chart titles are text only and some are linked to cells e.g.


='Sheet1'!$C$6


when I run the program listed below it removes the formulas from the charts.

The text only titles are uneffected. I loose the formula.


Can anyone adjust my program please ? to keep the formulas


Many Thanks if some one can work this one out !!!! Excel Ninja's Only !!!


Sub ChartTitles()


Dim ws As Worksheet

Dim ch As ChartObject


For Each ws In Worksheets

For Each ch In ws.ChartObjects


ch.Activate

ActiveChart.ClearToMatchStyle

ActiveChart.ChartStyle = 18

ActiveChart.ClearToMatchStyle

Next ch

Next ws


'Font Size and Colour in Charts


'Dim ws As Worksheet

'Dim ch As ChartObject


Dim Fnt As String

Dim FntSz As Double

Dim FntR As Integer

Dim FntG As Integer

Dim FntB As Integer


Fnt = "Rockwell (Body)" 'Set Font type

FntSz = 14 'Set Font Size


'Black Text


FntR = 0 'Set Font Color Red

FntG = 0 'Set Font Color Green

FntB = 0 'Set Font Color Blue


For Each ws In Worksheets

For Each ch In ws.ChartObjects


ch.Activate

ch.Chart.ChartTitle.Select

Selection.Format.TextFrame2.TextRange.Font.Name = Fnt

Selection.Format.TextFrame2.TextRange.Font.Size = FntSz

With Selection.Format.TextFrame2.TextRange.Font.Fill

.ForeColor.RGB = RGB(FntR, FntG, FntB)

.Transparency = 0

.Solid

Application.GoTo Range("a1")

End With

Next ch

Next ws


End Sub

 

[ianb]

My Soluton for each of the listboxes is as follows :


Sheets("sheet12).Select

ActiveSheet.ChartObjects("8").Activate

ActiveChart.ChartTitle.Select

'Selection.Characters.Text = Range("A1").Value

Selection.Characters.Text = Application.WorksheetFunction.Text(Range("c6").Value, "dd/mm/yyyy")


Hope this helps many......