Slicer click button handler to update a chart

[hcova]

Hi everyone.
I have created a PivotTable slicer to display different charts according the user's button selection.
I am using a combo chart and I need that 0 value in the primary axis and in the secondary axis to be at the same horizontal level.
Let explain it with the following chart. (The slicer by default gives me the below chart).

As you can see in the above combo chart, the 0 value of both Y axis are not at the same horizontal label

Then, this is what I need.......

Now, as you can see, the 0 value of the Y-axis coincides horizontally on both axes because I had to manually adjust the maximum and minimum values of the secondary axis

Question.
How can I write a VBA code (I know that Excel does not provide a slicer handler, just pivot tables handlers) to modify the secondary axis bounds each time a slicer button is clicked?

Second, where should I write the code? In the pivot table sheet? in the chart sheet? or in a vba module?

User Name	Claude
Row Labels	Profit	Accumulated Profit
2018	20,438	1,055,952
Qtr1	15,565	227,627
Qtr2	2,900	381,013
Qtr3	-344	276,657
Qtr4	2,316	170,655
2019	31,585	2,116,168
Qtr1	4,504	425,574
Qtr2	19,494	436,959
Qtr3	-10,902	589,310
Qtr4	18,488	664,325
2020	98,573	6,022,953
Qtr1	3,771	866,329
Qtr2	13,150	1,189,873
Qtr3	38,231	1,050,912
Qtr4	43,421	2,915,839
2021	26,825	11,022,305
Qtr1	1,477	2,469,449
Qtr2	15,979	2,825,337
Qtr3	-5,836	3,130,123
Qtr4	15,205	2,597,396
2022	-20,343	8,928,503
Qtr1	-3,941	2,404,155
Qtr2	-21,537	1,991,084
Qtr3	9,259	2,368,004
Qtr4	-4,124	2,165,261
2023	59,819	13,617,714
Qtr1	42,693	2,794,618
Qtr2	3,742	4,492,471
Qtr3	-5,850	2,797,980
Qtr4	19,234	3,532,646
2024	65,535	11,158,151
Qtr1	15,725	2,755,979
Qtr2	22,414	2,851,947
Qtr3	28,728	3,019,839
Qtr4	-1,332	2,530,385
Grand Total	282,431	53,921,746

 

[John_w]

How can I write a VBA code (I know that Excel does not provide a slicer handler, just pivot tables handlers) to modify the secondary axis bounds each time a slicer button is clicked?

Second, where should I write the code? In the pivot table sheet? in the chart sheet? or in a vba module?

The starting point would be the Worksheet_PivotTableUpdate handler in the sheet module of the pivot table sheet. For example, this simply displays the selected slicer item(s):

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    
    Dim theSlicer As Slicer
    Dim selectedSlicerItem As SlicerItem
    
    Set theSlicer = Target.Slicers("SysName")
    
    For Each selectedSlicerItem In theSlicer.SlicerCache.VisibleSlicerItems
        MsgBox "Item: " & selectedSlicerItem.Name
    Next
    
End Sub

We would probably need a sample workbook to help you further with this.