Dynamically Colored Shapes in Powerpoint using VBA

Question

this is my first post here,

I'm working on a presentation for a client, they want a color-coded map of Europe. The individual countries should be colored green-yellow-red depending on a certain risk-score. They (and me, too) thought this would be easily doable by somehow linking the PPT to an excel and then doing some conditional formatting. It seems we were very wrong.

I've first familiarized myself with Excel VBA to try and understand how VBA works. I created a map consisting of the European country shapes and named each shape accordingly. Then I created the dynamic coloring in excel using code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B2") = "opposed" Then
    ActiveSheet.Shapes.Range(Array("Austria")).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
    Else
    If Range("B2") = "undecided" Then
    ActiveSheet.Shapes.Range(Array("Austria")).Select
    Selection.ShapeRange.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent4
    Else
    If Range("B2") = "approving" Then
    ActiveSheet.Shapes.Range(Array("Austria")).Select
    Selection.ShapeRange.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6
    End If
    End If
    End If
End Sub

I just put the risk assessment ("opposed", "undecided", "approving") into a column, then ask what that value is, then change the color of the shape accordingly. The code works - if any value is changed, the map changes. I'm quite happy.

Now I need to make this work in Powerpoint. PP VBA seems to be quite different, and I couldn't find anything on how to make this work. I tried the following as a proof of concept:

Sub coloring()
If ActivePresentation.Slides(1).Shapes("Rect1").TextFrame.TextRange.Text.Value = "RED" Then ActivePresentation.Slides(1).Shapes("Rect2").Fill.ForeColor.RGB = RGB(255, 0, 0)
End Sub

I find it very hard to navigate PP VBA and it's really frustrating. How I'd like to do it: I'd like to create one (hidden) slide which contains all the information (in text boxes or a table with cells or something like that) and then create multiple slides which refer to the content of the hidden slide and display color-coded information based on the content on the hidden slide. But I don't really know where to start and how to make this work.

Any ideas are welcome

Steve Rindsberg · Answer

Since VBA for each app closely follows the app's own object model, each will seem odd if you're accustomed to a different app's OM but not the one you're working in.

Here's a working example. Your code was almost there:

Sub coloring()

' Using With/End With can save a lot of typing:
With ActivePresentation.Slides(1)

If .Shapes("Rect1").TextFrame.TextRange.Text = "RED" Then
    ' It's a good idea to make sure the fill is visible
    ' before setting it
    .Shapes("Rect2").Fill.Visible = True
    .Shapes("Rect2").Fill.ForeColor.RGB = RGB(255, 0, 0)
End If

End With

End Sub

Dynamically Colored Shapes in Powerpoint using VBA

1 Answers