Changing data source of chart in powerpoint vba

问题:

I have a bar Chart in PowerPoint and want to select the row (Category 1 - 4, see Screenshot 1) depending on my selection in the ComboBox. This is my Code so far:

  Private Sub ComboBox1_Change()
  With SlideShowWindows(1).View


    Select Case ComboBox1.Value
        Case "Category 1"

        Case "Category 2"

        Case Else

    End Select

End With
End Sub

I have no idea how to select source data when there is no seperate Excel-Sheet. There is just the "Excel-Sheet" in PowerPoint which is generated when inserting the Diagramm.

回答1:

Your chart is contained in a Shape object, and you can access the source data through Shape.Chart.ChartData.Workbook.Sheets(1), as this answer proposes.

I understood by your question that you want the chart to only display the Category selected. You can hide the rows you don't want to be displayed in the source data, and they will be hidden in the chart.

This populates ComboBox1 with Categories 1-4.

Private Sub ComboBox1_DropButtonClick()
    With ComboBox1
        If .ListCount = 0 Then
            .AddItem "Category 1", 0
            .AddItem "Category 2", 1
            .AddItem "Category 3", 2
            .AddItem "Category 4", 3
        End If
    End With
End Sub

And then you can hide the rows you don't want displayed with something like this.

Private Sub ComboBox1_Change()
    Dim shp As Shape: Set shp = ActivePresentation.SlideShowWindow.View.Slide.Shapes("Chart 3") ' Change to your chart name here
    Dim rw As Integer, j As Integer 

    Select Case ComboBox1.Value
        Case "Category 1"
            rw = 2
        Case "Category 2"
            rw = 3
        Case "Category 3"
            rw = 4
        Case "Category 4"
            rw = 5
        Case Else
            Exit Sub
    End Select

    For j = 2 To 5
        shp.Chart.ChartData.Workbook.sheets(1).Rows(j).Hidden = (j <> rw)
    Next j
End Sub

Screenshot