Have you ever created a VBA code to filter a graph?

[Curlsrx]

Hi Excel team!

This might be an easy fix for you?

• I have a graph.
• To hide/show my data I use a VBA code to activate chart filters.
• Chart Filters show/hide the desired data in my chart by using a macro to: Show OR Hide each respective data set in a series.
• For example, on the the 'Chart 1 Graph', show the data in row 1 and 2, but filter data in row 3 and so on.

My code works great on my computer running MS Office Excel 2013, but for some reason does not work on other machines running other versions of excel 2010 or 2016. It errors on (Activechart.FullSeriesCollection(2).IsFiltered = False) with: "compile error, method or data member not found". (note: I tried changing FullSeriesCollection to SeriesCollection but the code did not end up running thereafter)

Is there something in my code I'm missing or need to add?

Please advise!

'unprotect worksheet
  Sheets("Input").Unprotect

'Disable refresh
 Application.ScreenUpdating = False
  
'unhide sheets
  Sheets("Backend").Visible = True
  
  DoEvents
     
    Dim insheet As Worksheet
    Set insheet = ThisWorkbook.Sheets("Input")
    Dim backsheet As Worksheet
    Set backsheet = ThisWorkbook.Sheets("Backend")

    ' Filter Active
    If backsheet.Range("B2") = True Then
        insheet.Range("a1").Select
        ActiveSheet.ChartObjects("Chart 1").Activate
        Activechart.FullSeriesCollection(2).IsFiltered = False
    Else
        insheet.Range("a1").Select
        insheet.ChartObjects("Chart 1").Activate
        Activechart.FullSeriesCollection(2).IsFiltered = True
    End If
    
Range("A1").Select

ActiveWindow.Zoom = 100

'hide sheets
    Sheets("Backend").Visible = False
    Application.ScreenUpdating = True
'protect worksheet
  Sheets("Input").Protect

 

[Worf]

This works for me with Excel 2016:

Sub curl()
Dim insheet As Worksheet, backsheet As Worksheet
Set insheet = ThisWorkbook.Sheets("Input")
Set backsheet = ThisWorkbook.Sheets("Backend")
insheet.Unprotect
'unhide sheets
backsheet.Visible = True
DoEvents
' Filter Active
If backsheet.[B2] = True Then
    insheet.Activate
    [A1].Activate
    ActiveSheet.ChartObjects("Chart 1").Chart.FullSeriesCollection(2).IsFiltered = False
    MsgBox "true"
Else
    insheet.Activate
    [A1].Activate
    insheet.ChartObjects("Chart 1").Chart.FullSeriesCollection(2).IsFiltered = True
    MsgBox "false"
End If
[A1].Activate
ActiveWindow.Zoom = 100
Application.ScreenUpdating = True
insheet.Protect
End Sub

 

[Jon Peltier]

Filtering of chart data was introduced in Excel 2013, and FullSeriesCollection is used to include both filtered and unfiltered series. Excel 2010 does not support filtering or the full series collection.

I don't know why your code that worked in 2013 does not work in 2016. Nothing jumps out at me in your code, and Worf's code doesn't seem to change anything, except that he uses the bracket notation rather than the preferred but more verbose referencing of ranges.