CODEWITHSUNDEEP
excelchartsexcel-chartsvba
Prayag Saraiya
Prayag Saraiya

Reputation: 3

Pop Up Chart on a click from within a chart component

this a possibility question so please dont expect any code although I have tried my best to visualize it. example chart As you can see different regions here, now my question is, can I have a chart pop up when I click on "Asia" Column/Bar and the chart will show the concentration of all Asian countries.

Upvotes: 0

Views: 1055

Answers (1)

Rik Sportel
Rik Sportel

Reputation: 2679

Yes it is possible. A very basic implementation to capture these "click on a datapoint in a series" events is the following:

Class module MouseDownChart code:

Option Explicit
Private WithEvents p_Chart As Chart
Private Sub Class_Terminate()
    Set p_Chart = Nothing
End Sub
Public Sub setChart(ByRef Chart As Chart)
    Set p_Chart = Chart
End Sub
Private Sub p_Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) 'Capture the event
    Dim sertmp As Series
    Dim IDNum As Long
    Dim a As Long
    Dim B As Long
    p_Chart.GetChartElement x, y, IDNum, a, B
    If IDNum = 3 Then 'Clicked on a point in a chart series
        MsgBox "a is the clicked series: " & a
        MsgBox "B is the clicked datapoint: " & B
        'Write code to pop-up the right stuff here.
    End If
End Sub

And in a regular module:

Option Explicit
Public mdCht As MouseDownChart

Sub Init()
Dim ws As Worksheet
Dim co As ChartObject
Dim ch As Chart

Set ws = Worksheets("Sheet1")
Set co = Worksheets("Sheet1").ChartObjects(1)
Set ch = co.Chart
Set mdCht = New MouseDownChart

mdCht.setChart ch

End Sub

Keep in mind that a WorksheetChart can be referenced as "Chart", but any chart that is embedded on a worksheet like in your sample picture will always be contained in a ChartObject.

Upvotes: 1

Related Questions