CODEWITHSUNDEEP
excelvbaexcel-charts
Liuchess
Liuchess

Reputation: 1

Filtering out chart categories

I've been trying everyway I can to create a vba code that deselects a few categories if they aren't used. In this case I have a graph from January to December, and I want to deselect the months that haven't arrived yet. I have one cell, that tells me how many months passed based on year selection: For example, for 2023 it will give me 3 and for 2022 it will give me 12.

This can be the parameter to filter the categories from 1 to 3, for example.

Please help, I've tried a dozen of codes, none seemed to get close to work.

I've attached the images for context.

enter image description here

enter image description here

I tried TickSpacing as suggested by ChatGPT

Dim TickSpacing As Integer

' Deselect future months by setting the tick label spacing
 ActiveChart.Axes(xlCategory).TickLabelSpacing = Worksheets("Database").Range("BG32").Value

Upvotes: -1

Views: 399

Answers (1)

Liuchess
Liuchess

Reputation: 1

I finally did it, I case someone runs into the same problem here's the code I used:

Dim FullCategoryCollection As CategoryCollection
Dim ChartCategory As ChartCategory
Dim n As Long
    
    Set FullCategoryCollection = Worksheets("Home").ChartObjects("GastosTotaisDiv").Chart.ChartGroups(1).FullCategoryCollection
    
    For n = 1 To FullCategoryCollection.Count
        Set ChartCategory = FullCategoryCollection(n)
        If n > Worksheets("Database").Range("BG32").Value Then
        'Debug.Print ChartCategory.Name, ChartCategory.IsFiltered
        ChartCategory.IsFiltered = True
        Else
        ChartCategory.IsFiltered = False
        End If
    Next

In this case BG32 gives me the number of months to consider from 1 to 12, using a DateDif formula.

Thanks!

Upvotes: 0

Related Questions