VBA - Click on different slices of pie chart to run different macros

Question

Not sure if it's possible with VBA but I would like to use each part of a pie chart as buttons to run four different macros.

Furthermore, the pie chart is re-created every time (code shown below) when the values changes and so the sizes of the slices isn't fixed. As such, I don't think I can just put a similar shape on top of the slices and assign a macro to it.

Public Sub CreatePieChart()

Dim ws As Worksheet

Dim ch_shape As Shape
Dim lab As DataLabel
Dim x As Long, y As Long, w As Long, h As Long
Dim circ As Shape

Set ws = ThisWorkbook.Worksheets("Sheet1")

Set ch_shape = ws.Shapes.AddChart2

With ch_shape.Chart
    With .ChartArea
        .Format.Fill.ForeColor.RGB = RGB(244, 244, 244)
        .Height = 300
        .Width = 450
        
        .Left = 0
        .Top = 350
        
    End With
    
    .ChartType = xlPie
    .SetSourceData ws.Range("D14:E17")
    .HasTitle = False
    .HasLegend = False
    .ApplyDataLabels xlDataLabelsShowLabel, , , , , True, , True, , vbLf
    
    With .FullSeriesCollection(1).DataLabels
        .Position = xlLabelPositionOutsideEnd
        .NumberFormat = "0.0%"
    End With

End With

End Sub

Answer 1

You can make it a chart sheet and use the Chart_select event.

Select your data range and press F11.
Change the chart type to circle.

Open VBA Editor and open the diagram code and paste the following:

Private Sub Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    Debug.Print ElementID
    Debug.Print Arg1
    Debug.Print Arg2
    Debug.Print ""
End Sub

These three arguments tell you what part of the chart you pressed.
When I click on the yellow larger chart object I get:

 3 
 1 
 4 

And the blue:

3
1
1

I haven't done much more debugging in it but I assume 3 is the chart area because if I click on the white area I get 2,0,0.
Anyways, I think you will find a way with your work case.

Found a page with some information about the chart events https://peltiertech.com/chart-events-microsoft-excel/

Answer 2

Try the next way, please:

1. Insert a class module and name it "ChartEvClass". Copy the next code in its module:

Option Explicit

Public WithEvents EvtChart As Chart

Private Sub EvtChart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim elementId As Long, arg1 As Long, arg2 As Long
    Dim arrDL, i As Long
    
    ReDim arrDL(1 To ActiveChart.SeriesCollection(1).DataLabels.count)
    For i = 1 To ActiveChart.SeriesCollection(1).DataLabels.count
        arrDL(i) = Split(ActiveChart.SeriesCollection(1).DataLabels(i).Text, vbLf)(0)
    Next i
    With ActiveChart
        .GetChartElement x, y, elementId, arg1, arg2
        Call DoSomething(Application.Index(arrDL, arg2))        
    End With
End Sub

2. On top of a standard module (declarations area), create a Private variable:

Private clsEventCharts() As New ChartEvClass

3. In the same module, copy the next procedure (which can be called by an event, Sheet_Activate, for instance):

Sub ActivateChartsEvent()
  If ActiveSheet.ChartObjects.count > 0 Then
        ReDim clsEventCharts(1 To ActiveSheet.ChartObjects.count)
        Dim chtObj As ChartObject, i As Long: i = 1

        For Each chtObj In ActiveSheet.ChartObjects
            Set clsEventCharts(i).EvtChart = chtObj.Chart
            i = i + 1
        Next
    End If
End Sub

The above Sub may allocate the event to all existing charts. Run it and try playing with clicking on the chart slices. The event will return the slice label...

4. Copy the next code, too. It can be used to run something according to the returned label:

Sub DoSomething(strLabel As String)
    MsgBox strLabel
    'use the label to run whatever you need...
End Sub

The class code is dynamic, it should return the labels for how many they exist.

Please, test it and send some feedback.