make chart axis labels hyperlink to other pages

ijourneaux

New Member
Joined
Jul 9, 2018
Messages
22
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,699
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

New Member
Joined
Jul 9, 2018
Messages
22
Thank you Jon. I was able to use your article to accomplish what I needed to do. Great article!
 

ijourneaux

New Member
Joined
Jul 9, 2018
Messages
22
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,699
Is there a way you could share the workbook?
 

ijourneaux

New Member
Joined
Jul 9, 2018
Messages
22
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

New Member
Joined
Jul 9, 2018
Messages
22
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.
Code:
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,699
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

New Member
Joined
Jul 9, 2018
Messages
22
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

Board Regular
Joined
Mar 30, 2018
Messages
76
Why not just put transparent shapes over labels that are connected to a macro?
 

Forum statistics

Threads
1,077,855
Messages
5,336,788
Members
399,102
Latest member
chudson1

Some videos you may like

This Week's Hot Topics

Top