Formulas for Chart Titles - go missing in Charts when I run my VBA program.

[ianb]

Hi,


When I run this program the chart forulas go missing any ideas on how I can keep the formula.


excample of a formula is : ='Dashboard (Individual)'!$A$60


I have over 20 of these on various sheets.


listed bleow is my program.

[pre]

Sub ChartTitlesLight()

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

[/pre]

 

[NARAYANK991]

Hi Ian ,


Change the following lines of code :

[pre]

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
to the following :

Selection.Font.Name = Fnt
Selection.Font.Size = FntSz
Selection.Font.Color = RGB(FntR, FntG, FntB)
Application.GoTo Range("a1")

[/pre]
Narayan

 

[ianb]

Genius... Thank you so much. it has save so much time for me....


For Each ch In ws.ChartObjects


ch.Activate

ch.Chart.ChartTitle.Select

With Selection.Font.Name = Fnt

Selection.Font.Size = FntSz

Selection.Font.Color = RGB(FntR, FntG, FntB)

Application.GoTo Range("a1")

End With