make chart axis labels hyperlink to other pages

ijourneaux

New Member
Joined
Jul 9, 2018
Messages
23
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,728
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
23
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
23
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,728
Is there a way you could share the workbook?
 

ijourneaux

New Member
Joined
Jul 9, 2018
Messages
23
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
23
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,728
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
23
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
77
Why not just put transparent shapes over labels that are connected to a macro?
 

Forum statistics

Threads
1,082,065
Messages
5,362,976
Members
400,702
Latest member
oliviaalx

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top