How to show hidden categories on an Excel Pie chart using VBA

Question

I've spent hours researching this question with no results and the Excel object model posted in MSDN has not been any help. I have an Excel spreadsheet with a prebuilt pie chart that is opened and manipulated via MS-Access VBA. The data to be displayed is populated and the resulting chart is used in an Access report. I did not build the original spreadsheet but I've modified it for my own use. A user pointed out that one of the categories was missing. Right-clicking on the chart and clicking on "select data" brings up the "Select Data Source" dialog box. The categories are listed on the right-hand side. Several were unchecked. I've manually selected them all and now the chart is correct.

The question is: I might want to unselect and (more importantly) re-select one of these categories using VBA code. I'm pretty sure I can unselect a category but once that happens it looks like it's impossible to reselect. Can a previously unselected category be re-selected using VBA?

Answer 1

Turn on the macro recorder, then manually filter out some categories and apply the filter, then manually reset the filters.

In my pie chart, I had categories "a" through "f", and I manually filtered out "b" and "d", then restored them. Here's my recorded code:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveChart.ChartGroups(1).FullCategoryCollection(2).IsFiltered = True
    ActiveChart.ChartGroups(1).FullCategoryCollection(4).IsFiltered = True
    ActiveChart.ChartGroups(1).FullCategoryCollection(2).IsFiltered = False
    ActiveChart.ChartGroups(1).FullCategoryCollection(4).IsFiltered = False
End Sub

To reset the category filters in your pie chart, run this (which I wrote based on the recorded code above):

Sub ResetCategoryFilters()
  Dim iPt As Long, nPts As Long
  With ActiveChart.ChartGroups(1)
    nPts = .FullCategoryCollection.Count
    For iPt = 1 To nPts
      .FullCategoryCollection(iPt).IsFiltered = False
    Next
  End With
End Sub