make chart axis labels hyperlink to other pages

[ijourneaux]

I have an Excel bar chart I would like to make the chart axis labels hyperlink to other other charts that contain additional details about the specific tag of interest. Is this possible?

 

[Jon Peltier]

I'd have to see the initial chart to know exactly how to help. I can't think of a way to hyperlink from the axis labels. But you could click on a point for that axis category and write some VBA to show a particular other chart.

It's complicated. I wrote an article about Chart Events in Microsoft Excel, which might get you started. If you post back with some details, I or somebody else could assist further.

 

[ijourneaux]

Thank you Jon. I was able to use your article to accomplish what I needed to do. Great article!

 

[ijourneaux]

Jon
I thought I have everything implemented correctly and things were working but it is inconsistent. I have a horizontal bar chart. When I click on one of the bars, sometimes it comes back correctly but recently is always comes back as 19 (plot area). I must be missing something obvious.

 

[Jon Peltier]

Is there a way you could share the workbook?

 

[ijourneaux]

As usual I am alwasy the "special case" <grin>.

Everything seems to be working today. Same spreadsheet no code changes. Could it be related to screen resolution. Today I am at my desktop with 2 large screens. This weekend, when I was having problems, I was the same computer but using laptop screen. When I was having difficulties, when I clicked on the bar in a bar chart (and the bar was selected), the event came back as plot area.

 

[ijourneaux]

It is back to not working. When it doesn't work, Arg2 is 0 and element ID 19 is which skips over the code that send the user to another worksheet. Unfortunately, I can't share my workbook but here is the code. You can see that I scavenged the code directly from your reference.

Private Sub Chart_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 myX As Variant, myY As Double
 
    Dim FriendlyTagName As String
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim RowValue As Long
    Dim Tag As String
    Dim TagUnits As String
  
    With ActiveChart
        ' Pass x & y, return ElementID and Args
        .GetChartElement x, y, ElementID, Arg1, Arg2
 
        ' Did we click over a point or data label?
        If ElementID = xlSeries Or ElementID = xlDataLabel Then
            If Arg2 > 0 Then
                ' Extract x value from array of x values
                myX = WorksheetFunction.index _
                    (.SeriesCollection(Arg1).XValues, Arg2)
                ' Extract y value from array of y values
                myY = WorksheetFunction.index _
                    (.SeriesCollection(Arg1).Values, Arg2)
 
                ' Display message box with point information
'                MsgBox "Series " & Arg1 & vbCrLf _
'                    & """" & .SeriesCollection(Arg1).Name & """" & vbCrLf _
'                    & "Point " & Arg2 & vbCrLf _
'                    & "X = " & myX & vbCrLf _
'                    & "Y = " & myY
            End If
            bFollowHyperlink = True
            sSourceChart = "TornadoChart"
            FriendlyTagName = Left(myX, InStr(myX, "(") - 2)
            FirstRow = 2
            LastRow = Worksheets("TagGroups").Range("A65536").End(xlUp).row
            RowValue = Application.Match(FriendlyTagName, Worksheets("TagGroups").Range("c1:c501"), 0)
            Tag = Worksheets("TagGroups").Cells(RowValue, 2)
            TagFriendlyName = Worksheets("TagGroups").Cells(RowValue, 3)
            TagUnits = Worksheets("TagGroups").Cells(RowValue, 13)
            Application.ScreenUpdating = False
            Worksheets("Trend").Activate
            Worksheets("Trend").Cells(1, 1) = Tag
            Worksheets("Trend").Cells(1, 3) = sServerName
            Worksheets("Trend").Cells(1, 4) = "*"
            Worksheets("Trend").Cells(2, 1) = TagFriendlyName
            Worksheets("Trend").Cells(3, 1) = TagUnits
            Worksheets("Trend").Range("G44").Select
            Worksheets("Trend").Button1Day_Click
            Application.ScreenUpdating = True
        End If
    End With
End Sub

 

[Jon Peltier]

Element 19 is the plot area. This means you didn't click on any specific point of any specific series. If you click on a point, Arg1 is the series index and Arg2 is the point index.

 

[ijourneaux]

Element 19 is the plot area. This means you didn't click on any specific point of any specific series. If you click on a point, Arg1 is the series index and Arg2 is the point index.

I agree but I am definitely clicking on the bar in the chart.

 

[Swayzy]

Why not just put transparent shapes over labels that are connected to a macro?