Apply Colour Changes to Excel Chart in PowerPoint using VBA

Gemstorm
Gemstorm used Ask the Experts™
on
Hi,
Through VBA how do I change the colours of the data series of a chart that's on a Powerpoint slide or alternatively can I apply a .CRTX template to an existing chart - again through VBA.

Many Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
ActiveSheet.ChartObjects("Chart1").Activate
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Interior.Color = RGB(255, 0, 0)

Author

Commented:
Sorry maybe I wasn't clear enough - I'm working in PowerPoint - your solution uses ActiveSheet to reference the chart. I won't be working in Excel so I need to reference the chart through PowerPoint.

Commented:
for example you can use following example in powerpoint
Dim oGraph As Graph.Chart
Set oGraph = oPPTShape.OLEFormat.Object
oGraph.SeriesCollection(1).Interior.ColorIndex = 3

Author

Commented:
Hi ukerandi,
thanks for the suggestion. However I'm getting the following error

Dim oGraph As Graph.Chart: Compile error - user defined type not defined

Commented:
check my code
before run my programme
Goto Developer and Visual basic menue,click on it and add module and paste my code below

On the Tools menu, click References.

In the References dialog box, scroll until you see Microsoft Excel 14.0 Object Library,
Microsoft Powerpoint 12.0 Object Library and  Microsoft Office 12.0
select it, and then click OK.

Close the VBE.
then goto Developer menue and click on visual basic menue -. then click on to run
Sub CreateChart()

Dim myChart As Chart
   Dim gChartData As ChartData
    Dim gWorkBook As Excel.Workbook
  Dim gWorkSheet As Excel.Worksheet      ' Create the chart and set a reference to the chart data.
   Set myChart = ActivePresentation.Slides(1).Shapes.AddChart.Chart
  Set gChartData = myChart.ChartData
' Set the Workbook and Worksheet references.
 Set gWorkBook = gChartData.Workbook
Set gWorkSheet = gWorkBook.Worksheets(1)      ' Add the data to the workbook.
  gWorkSheet.ListObjects("Table1").Resize gWorkSheet.Range("A1:B5")
  gWorkSheet.Range("Table1[[#Headers],[Series 1]]").Value = "Items"
  gWorkSheet.Range("A2").Value = "Coffee"
 gWorkSheet.Range("A3").Value = "Soda"
 gWorkSheet.Range("A4").Value = "Tea"
  gWorkSheet.Range("A5").Value = "Water"
   gWorkSheet.Range("B2").Value = "1000"
 gWorkSheet.Range("B3").Value = "2500"
 gWorkSheet.Range("B4").Value = "4000"
   gWorkSheet.Range("B5").Value = "3000"
   ' Apply styles to the chart.
   With myChart
   .ChartStyle = 4
 .ApplyLayout 4
.ClearToMatchStyle

  End With      ' Add the axis title.
  With myChart.Axes(xlValue)
 .HasTitle = True
  .AxisTitle.Text = "Units"
 End With      'myChart.ApplyDataLabels
  ' Clean up the references.
 Set gWorkSheet = Nothing
 ' gWorkBook.Application.Quit
   Set gWorkBook = Nothing
 Set gChartData = Nothing
Set myChart = Nothing
 End Sub

Open in new window

Commented:
For your Error  "oGraph As Graph.Chart: Compile error - user defined type not defined "
please follow this instructions

On the Tools menu, point to Macros, and then click Visual Basic Editor.
On the Tools menu, click References.
In the "References - VBAProject" dialog box, click to select the "Microsoft Graph 8.0 Object Library" check box.
Click OK.
Commented:
check this code,its a good example
Sub ColorPieSlices()
' Copyright 1999 MrExcel.com
' This macro will re-color the pie slices in a chart
' So that slices for a specific category are similarly colored
' Select the chart before calling the macro
'    
' Find the number of pie slices in this chart
    NumPoints = ActiveChart.SeriesCollection(1).Points.Count
    ' Loop through each pie slice
    For x = 1 To NumPoints
        ' Save the label currently attached to this slice
        If ActiveChart.SeriesCollection(1). _
            Points(x).HasDataLabel = True Then
                SavePtLabel = ActiveChart.SeriesCollection(1) _
                    .Points(x).DataLabel.Text
        Else
            SavePtLabel = ""
        End If
        ' Assign a new data label of just the point name
        ActiveChart.SeriesCollection(1).Points(x).ApplyDataLabels Type:= _
            xlDataLabelsShowLabel, AutoText:=True
        ThisPt = ActiveChart.SeriesCollection(1).Points(x).DataLabel.Text
        ' Based on the label of this slice, set the color
        Select Case ThisPt
            Case "Freshman"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 3
            Case "Sophomore"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 4
            Case "Junior"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 5
            Case "Senior"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 6
            Case Else
                ' Add code here to handle an unexpected label
        End Select
        ' Return the label to it's original pre-macro state
        ActiveChart.SeriesCollection(1). _
            Points(x).DataLabel.Text = SavePtLabel
    Next x
End Sub

Open in new window

TracyVBA Developer

Commented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for ukerandi's comment http:/Q_27238476.html#36367997

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Author

Commented:
Apologies for not closing this question. I have closed the question properly.

Author

Commented:
Everything suggested above will work with PowerPoint 2003 as it references charts through the Chart Object. However PowerPoint 2007 and 2010 use the Excel Chart object - despite exhaustive searching there doesn't appear to be any way to manipluate a chart idirectly in PowerPoint 2007/2010.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial