excelvbaexcel-2016

FullCategoryCollection works only on first chart


So, this code does what I want, to reset the categories in a graph, on the first. It runs through all charts, I get the index right, use the name of the graph, at this point I'm not sure what to try anymore. Any light to shed on my poor mortal coil?

Public countIndex As Integer
Sub Botão1_Clique()
    
    Dim i As Integer
    Dim x As Integer
    Dim countChart As Long
    Dim check As Variant
    Dim wkdad As Worksheet
    Dim y As Integer
    Dim z As Long
    
    On Error Resume Next
    
    Set wkdad = ThisWorkbook.Sheets("Planilha1")
    i = 25
    y = 1
    countIndex = 0
    check = wkdad.Cells(i, 7)
    Cad_Vendas.ComboBox1.Clear
    
    Do While check <> ""
        With Cad_Vendas.ComboBox1
            .AddItem wkdad.Cells(i, 7)
        End With
        wkdad.ChartObjects("Gráfico " & y).Activate
        z = ActiveChart.Parent.Index
        With ActiveChart.ChartGroups(z)
          countChart = .FullCategoryCollection.Count
          For x = 1 To countChart
            .FullCategoryCollection(x).IsFiltered = False
          Next
        End With
        i = i + 1
        y = y + 1
        countIndex = countIndex + 1
        check = wkdad.Cells(i, 7)
    Loop

    
    Cad_Vendas.Show
    Cad_Vendas.ComboBox1.ListIndex = 0
End Sub

Solution

  • Public countIndex As Integer
    Sub Botão1_Clique()
    
    Dim i As Integer
    Dim x As Integer
    Dim countChart As Long
    Dim check As Variant
    Dim wkdad As Worksheet
    Dim y As Integer
    
    On Error Resume Next
    
    Set wkdad = ThisWorkbook.Sheets("Planilha1")
    i = 25
    y = 1
    countIndex = 0
    check = wkdad.Cells(i, 7)
    Cad_Vendas.ComboBox1.Clear
    
    Do While check <> ""
        With Cad_Vendas.ComboBox1
            .AddItem wkdad.Cells(i, 7)
        End With
        wkdad.ChartObjects("Gráfico " & y).Activate
        With ActiveChart.ChartGroups(1)
          countChart = .FullCategoryCollection.Count
          For x = 1 To countChart
            .FullCategoryCollection(x).IsFiltered = False
          Next
        End With
        i = i + 1
        y = y + 1
        countIndex = countIndex + 1
        check = wkdad.Cells(i, 7)
    Loop
    
    
    Cad_Vendas.Show
    Cad_Vendas.ComboBox1.ListIndex = 0
    End Sub
    

    This code works. The problem was that that the ChartGroups(z) was suposed to be always (1), so the variable z was making it only work with the first chart, as every chart had its own ChartGroups index starting at 1.