Can graphs have a hyperlink filter?

[GH1982]

I' m not sure if this is possible but can you click on a column in a graph which will then take you to another tab which would show you filtered data?
To explain a bit better, I have a spreadsheet that gives me information on machine breakdowns for the year. I then have another tab that shows various graphs relating to the breakdowns. The machine has 26 posts (to make it easy, we can call a to z) and 1 of the graphs shows the 5 posts that had the highest number of breakdowns. What i would like to do is click on any one of the 5 posts in the graph, let's say post G as it had the highest number of breakdowns in the previous week, which will then either open up a new tab with the breakdowns for just post G or open up the breakdowns tab and only have the post G breakdowns showing. Also, the graph will change week to week so the breakdowns showing for post G would have to be for the specific week as in, last week was week 5 so it would only show the breakdowns on post G for week 5.

Really hope this is possible!

Thanks

Greg

 

[Worf]

• Yes, it is possible. The preliminary example below shows information related to a clicked single data point, which we will use later.
• Firstly, run the Initialize App Events routine and then click a chart point at the Charts sheet.
• I will be back soon with something more specific to your request.
• Do you have a table on the breakdown sheet? What is its layout?

https://www.dropbox.com/s/l145ycyx2qmw0tq/GH_charts.xlsm?dl=0

' class module


Public WithEvents EventChart As Chart


Private Sub EventChart_Select(ByVal ElementID As Long, ByVal Arg1&, ByVal Arg2&)
Dim xv, yv, s As Series
If ElementID = xlSeries And Arg2 <> -1 Then
    Set s = ActiveChart.SeriesCollection(Arg1)
    xv = s.XValues
    yv = s.Values
    MsgBox "Series: " & s.Name & vbLf & "X value: " & xv(Arg2) & _
    vbLf & "Y value: " & yv(Arg2)
End If
End Sub

' standard module
' by Jon Peltier
Dim clsAppEvent As New CAppEvent, clsChartEvent As New CChartEvent
Dim clsChartEvents() As New CChartEvent


Sub InitializeAppEvents()
Set clsAppEvent.EventApp = Application
Set_All_Charts
End Sub


Sub TerminateAppEvents()
Set clsAppEvent.EventApp = Nothing
Reset_All_Charts
End Sub


Sub Set_All_Charts()
' Enable events for active sheet if sheet is a chart sheet
If TypeName(ActiveSheet) = "Chart" Then Set clsChartEvent.EventChart = ActiveSheet
' Enable events for all charts embedded on a sheet
' Works for embedded charts on a worksheet or chart sheet
If ActiveSheet.ChartObjects.Count > 0 Then
    ReDim clsChartEvents(1 To ActiveSheet.ChartObjects.Count)
    Dim chtObj As ChartObject, chtnum%
    chtnum = 1
    For Each chtObj In ActiveSheet.ChartObjects
        Set clsChartEvents(chtnum).EventChart = chtObj.Chart
        chtnum = chtnum + 1
    Next
End If
End Sub


Sub Reset_All_Charts()
' Disable events for all charts previously enabled together
Dim chtnum%
On Error Resume Next
Set clsChartEvent.EventChart = Nothing
For chtnum = 1 To UBound(clsChartEvents)
    Set clsChartEvents(chtnum).EventChart = Nothing
Next
End Sub

 

[GH1982]

Hi Worf,

Thanks for your reply.

I will have access to my work laptop tomorrow so I can send you the spreadsheet so you a understand better.

I don't have a table on the breakdowns sheet, however it is on a different tab in the same spreadsheet.

Looking at your example, my thought is if I click on the Chart tab and click on Alpha in column D, let's pretend it's Post D and the number is 7, let's say it is 7 breakdowns. It would then jump to the Data tab and show me only the 7 breakdowns that happened on Post D. It would filter them put from the other breakdowns on all the other posts as well as Beta and Gamma on Post D.

I hope this makes sense?

Thanks

Greg

 

[Worf]

This example filters a table based on what was clicked on the chart:

https://www.dropbox.com/s/ubz3hu48vgpati9/GH_charts2.xlsm?dl=0

' class module


Public WithEvents EventChart As Chart


Private Sub EventChart_Select(ByVal ElementID&, ByVal Arg1&, ByVal Arg2&)
Dim xv, yv, s As Series, rng As Range
If ElementID = xlSeries And Arg2 <> -1 Then
    Set s = ActiveChart.SeriesCollection(Arg1)
    xv = s.XValues
    yv = s.Values
    MsgBox "Series: " & s.Name & vbLf & "X value: " & xv(Arg2) & _
    vbLf & "Y value: " & yv(Arg2)
    Sheets("data").Activate
    Set rng = ActiveSheet.ListObjects("table1").Range
    With rng
        .AutoFilter 1, "=" & xv(Arg2), xlAnd
        .AutoFilter 2, "=" & s.Name, xlAnd
    End With
End If
End Sub