Setting Font size in a Chart

[Rathish]

Hi I am trying to change the font size of the Charts and not abble to do so. Can somebody help me on the same.

I want to change the chart font size
and
want to remove the gridlines from the chart. Given below is the code which i did till now. Please help.



Global r As Double
Global g As Double
Global b As Double

Sub ChartPPT()
Dim chSeries As Series
e = ActiveWindow.View.Slide.SlideIndex
Set cht = ActivePresentation.Slides(e).Shapes(1).Chart
Name$ = ActiveWindow.Selection.ShapeRange(1).Name
e = ActiveWindow.View.Slide.SlideIndex
Set cht = ActivePresentation.Slides(e).Shapes(Name$).Chart
'ActivePresentation.Slides(e).Shapes(Name$).Chart.
'ActiveWindow.Selection.ShapeRange(1).Chart.ChartArea.Format.TextFrame2.Tex tRange.Font.Name
With cht
ser_cnt = .SeriesCollection.Count
'With ActivePresentation.Slides(e).Shapes(1).Chart..TextFrame.TextRange.Font
.Size = 16
.Bold = msoTrue
End With
For j = 1 To ser_cnt
If .HasAxis(xlValue, xlSecondary) = True Then
'.Chart.Axes(xlSecondary).
End If
If .HasAxis(xlValue, xlPrimary) = True Then
'objCht.SeriesCollection(j).Points(ser_cnt).DataLabel.Font.Size = 8
End If
If j = 1 Then
r = 64
g = 152
b = 173
ElseIf j = 2 Then
r = 191
g = 221
b = 228
ElseIf j = 3 Then
r = 170
g = 98
b = 170
ElseIf j = 4 Then
r = 227
g = 202
b = 227
ElseIf j = 5 Then
r = 189
g = 180
b = 148
ElseIf j = 6 Then
r = 233
g = 233
b = 219
ElseIf j = 7 Then
r = 155
g = 201
b = 64
ElseIf j = 8 Then
r = 222
g = 234
b = 191
ElseIf j = 9 Then
r = 64
g = 102
b = 170
ElseIf j = 10 Then
r = 191
g = 204
b = 227
End If
.SeriesCollection(j).Format.Fill.ForeColor.RGB = RGB(r, g, b)
'.SeriesCollection(j).Format.Font
Next j
End With
'End Function
End Sub

[John Wilson]

Which Text??

Charts don't have a textframe you need to change specific text

These should all be valid (if the chart has a Title)



With cht.Axes(xlValue).TickLabels.Font
.Bold = True
.Size = 16
End With
With cht.Axes(xlCategory).TickLabels.Font
.Bold = True
.Size = 16
End With
With cht.ChartTitle.Font
.Bold = True
.Size = 16
End With
You need to declare all of the variables
Also
r,g,b should be Longs not doubles
Name$ is NOT an allowed name for a variable and is not needed you have already set a reference to cht so
Name$ = ActiveWindow.Selection.ShapeRange(1).Name
and
Set cht = ActivePresentation.Slides(e).Shapes(Name$).Chart
can be deleted

[Rathish]

Hi,
Thank you very much for a quick reply. Thanks for pointing out my errors so that i could learn new things.
In my previouse question fonts can be set however the second part "want to remove the gridlines (horizontal and vertical) from the chart."is pending can you please help me for the same.

Regards
Radish

[John Wilson]

Set the gridlines to false for the axes

example


Sub ChartPPT()
Dim chSeries As Series
Dim r As Long
Dim g As Long
Dim b As Long
Dim e As Long
Dim j As Long
Dim ser_cnt As Long
Dim cht As Chart
e = ActiveWindow.View.Slide.SlideIndex
Set cht = ActivePresentation.Slides(e).Shapes(1).Chart
With cht
ser_cnt = .SeriesCollection.Count
With .Axes(xlValue)
.TickLabels.Font.Size = 16
.TickLabels.Font.Bold = msoTrue
.HasMajorGridlines = False
End With
With .Axes(xlCategory)
.TickLabels.Font.Size = 16
.TickLabels.Font.Bold = msoTrue
.HasMajorGridlines = False
End With
If .HasTitle = True Then
With .ChartTitle.Font
.Bold = True
.Size = 24
End With
End If
For j = 1 To ser_cnt
Select Case j
Case Is = 1
r = 64
g = 152
b = 173
Case Is = 2
r = 191
g = 221
b = 228
Case Is = 3
r = 170
g = 98
b = 170
Case Is = 4
r = 227
g = 202
b = 227
Case Is = 5
r = 189
g = 180
b = 148
Case Is = 6
r = 233
g = 233
b = 219
Case Is = 7
r = 155
g = 201
b = 64
Case Is = 8
r = 222
g = 234
b = 191
Case Is = 9
r = 64
g = 102
b = 170
Case Is = 10
r = 191
g = 204
b = 227
End Select
On Error Resume Next
.SeriesCollection(j).Format.Fill.ForeColor.RGB = RGB(r, g, b)
.SeriesCollection(j).Border.Color = RGB(r, g, b)
Next j
End With
End Sub

[Rathish]

Hi,
I run the code given by you on my Office PC. It was working fine, However when i tried the same code on my laptop it is giving Run Time Error 91. Using office 2007 in both PCs

I suspect if there is any other Reference which is required other than the given below:
In PPT -
1) Visual Basic for Applications
2) Microsoft Powerpoint 12.0 object Library
3) Microsoft Office 12.0 Object Library
4) Microsoft Graph 12.0 Object Library
5) Microsoft Excel 12.0 Object Library
6) OLE Automation

Or else please advice what else i can do.

Regards
Radish

[John Wilson]

Error 91 would probably mean that the cht object variable was not set to a chart. It's not likely to be a reference. I haven't set a reference to Excel or Microsoft Graph (I would remove Graph)

Is shapes 1 actually a chart.

what line is highlighted if you debug?

[Rathish]

I am getting error on the given below line.
I tried some of References and manage not to get Error 91. However i am getting a compile error
"Method or Data member not found" Highlighting .Chart on the given below line of code.
Set cht = ActivePresentation.Slides(e).Shapes(1).Chart

I am trying on the charts which can be inserted from the insert chart options.

[John Wilson]

Only thing I can think of is - Are you up to date with sevice packs?? (SP3)

You should NOT need a reference to Excel or Graph.

[Rathish]

Hi,
Would like to know How can i change the colours of slice and font size of a pie chart.
bcoz the code that you helped me before works for bar and LIne chart perfectly on my office PC. Can you please help me out for the same. Please....

Regards
Radhish

[John Wilson]

With a PIe there are no axes so you need to remove that part of the code. There is one series with a number of points.

After this you may have to hire me as we have a busy few weeks!


Sub PieChartPPT()
Dim r As Long
Dim g As Long
Dim b As Long
Dim e As Long
Dim j As Long
Dim Points_cnt As Long
Dim cht As Chart
e = ActiveWindow.View.Slide.SlideIndex
Set cht = ActivePresentation.Slides(e).Shapes(1).Chart
With cht
Points_cnt = .SeriesCollection(1).Points.Count
' With .Axes(xlValue)
' .TickLabels.Font.Size = 16
' .TickLabels.Font.Bold = msoTrue
' .HasMajorGridlines = False
' End With
' With .Axes(xlCategory)
' .TickLabels.Font.Size = 16
' .TickLabels.Font.Bold = msoTrue
' .HasMajorGridlines = False
' End With
If .HasTitle = True Then
With .ChartTitle.Font
.Bold = True
.Size = 24
End With
End If
For j = 1 To Points_cnt
Select Case j
Case Is = 1
r = 64
g = 152
b = 173
Case Is = 2
r = 191
g = 221
b = 228
Case Is = 3
r = 170
g = 98
b = 170
Case Is = 4
r = 227
g = 202
b = 227
Case Is = 5
r = 189
g = 180
b = 148
Case Is = 6
r = 233
g = 233
b = 219
Case Is = 7
r = 155
g = 201
b = 64
Case Is = 8
r = 222
g = 234
b = 191
Case Is = 9
r = 64
g = 102
b = 170
Case Is = 10
r = 191
g = 204
b = 227
End Select
On Error Resume Next
.SeriesCollection(1).Points(j).Format.Fill.ForeColor.RGB = RGB(r, g, b)
.SeriesCollection(1).Points(j).Border.Color = RGB(r, g, b)
Next j
End With
End Sub

[Rathish]

Thank you very much John for your help. If i could hire you i would have done well before. This is just for my team and my boss is not going to pay me for this and neither my team.
But personally i would like to thank you;
from you i not only got help but i gained knowledge as well.
I am new to this i don't know much about macros. But still i am trying; i want this tool for me and people around me which will actually ease our life.
Thanks again.....

[John Wilson]

It's OK I was joking! Keep asking./

[Rathish]

HI,
I am facing an issue on some PC's to run the code that you helped me to create chart.
Macro says method or data member not found.and highlight.Chart in the given below line.

Set cht = ActivePresentation.Slides(e).Shapes(1).Chart

I checked for refernces in PPT:
1) Visual Basic For Application
2) Microsoft Power point 12.0 Object Library
3) OLE automation
4) Microsoft Excel 12.0 Object Library

References in Excel:
1) Visual Basic For Application
2) Microsoft Excel 12.0 Object Library
3) OLE automation
4) Microsoft Office 12.0 Object Library


However if i check given below in excel (I doubt) and close application. After reopening excel i find it unchecked.
5) Microsoft Power point 12.0 Object Library
6) Microsoft Graph 12.0 Object Library

How should i move forward on this please guide me.



Sub PieChartPPT()
Dim r As Long
Dim g As Long
Dim b As Long
Dim e As Long
Dim j As Long
Dim Points_cnt As Long
Dim cht As Chart
e = ActiveWindow.View.Slide.SlideIndex
Set cht = ActivePresentation.Slides(e).Shapes(1).Chart
With cht
Points_cnt = .SeriesCollection(1).Points.Count
' With .Axes(xlValue)
' .TickLabels.Font.Size = 16
' .TickLabels.Font.Bold = msoTrue
' .HasMajorGridlines = False
' End With
' With .Axes(xlCategory)
' .TickLabels.Font.Size = 16
' .TickLabels.Font.Bold = msoTrue
' .HasMajorGridlines = False
' End With
If .HasTitle = True Then
With .ChartTitle.Font
.Bold = True
.Size = 24
End With
End If
For j = 1 To Points_cnt
Select Case j
Case Is = 1
r = 64
g = 152
b = 173
Case Is = 2
r = 191
g = 221
b = 228
Case Is = 3
r = 170
g = 98
b = 170
Case Is = 4
r = 227
g = 202
b = 227
Case Is = 5
r = 189
g = 180
b = 148
Case Is = 6
r = 233
g = 233
b = 219
Case Is = 7
r = 155
g = 201
b = 64
Case Is = 8
r = 222
g = 234
b = 191
Case Is = 9
r = 64
g = 102
b = 170
Case Is = 10
r = 191
g = 204
b = 227
End Select
On Error Resume Next
.SeriesCollection(1).Points(j).Format.Fill.ForeColor.RGB = RGB(r, g, b)
.SeriesCollection(1).Points(j).Border.Color = RGB(r, g, b)
Next j
End With
End Sub