Trouble with a class module / hyperlinking chart objects

[mrjakob]

Hi everyone,

I'm new to the forum and this is my first post. I've tried to look for existing answers to my questions but either they don't exist or I just don't understand the answers. English is not my native tongue, so please bear with me if I dont express my self correctly.

The challenge:
I need to get the individual "pies" inside a pie chart to link to different webpages.

I'm definately not "fluent" in VBA, i'm kind of a beginner - so I browsed the web and someone came up with this:

Option Explicit

Public WithEvents CHT As Chart

Private Sub Workbook_Open()
    Set CHT = ActiveSheet.ChartObjects(1).Chart
End Sub

Private Sub CHT_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    On Error GoTo Fin
    If Selection.Name = "series1" Then
Range("A1").Select<---- I'm putting the hyperlink in this cell
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    End If
Fin:
End Sub

But I'm not sure if I am using it correctly. I'm placing it inside a class module, and named the data for my chart 'series1', but I keep ketting the error message:

"Cannot run the macro 'chart.xlsm!Diagram2_Click'. The macro may not be available in this workbook or all macros may be disabled."

And I'm allowing all macros... Do anyone of you know what I am doing wrong?

Any help is greatly appreciated

Thanks in advance.

 

[mrjakob]

Thanks, good point.

I've changed the code to:


Option Explicit


Public WithEvents CHT As Chart


Private Sub Workbook_Open()
Set CHT = ActiveSheet.ChartObjects(1).Chart
End Sub


Private Sub CHT_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
On Error GoTo Fin
If Selection.Name = "series1" Then
Application.Goto ActiveWorkbook.Sheets("Ark2").Range("A1")
ElseIf Selection.Name = "series2" Then
Application.Goto ActiveWorkbook.Sheets("Ark3").Range("A1")
End If
Fin:
End Sub




But now it just won't run. No finger-marker when I hover over the chart, nothing happens when I click it. (I have saved and reopened).

Thanks in advance.

 

[RoryA]

I suspect those names don't match your actual series names (the test is case sensitive).

 

[mrjakob]

I've tried to fix it, and reducing it to series1 for simplicity

As you see on the screenshot, even though I only select one of the objects (the blue one) the whole chart data range gets selected. I've changed the named range as you can see in the namecontrolling box so it fits, but even that won't work either.

 

[RoryA]

The selection.name part will return something like S1P1 (which means Series 1 Point 1), not the name of the range containing the data.