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.TextRange.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