How to assign a macro to a data point on chart

Originally Posted by Andy Pope

Coorect, you can not directly assign macros to datapoints or any other chart element.
You have to use chart events and then determine whether the selected item is the element to trigger as macro to run.

For chart events see,
http://www.computorcompanion.com/LPMArticle.asp?ID=221

Thanks Andy, I actually looked at the same article last night but as I mentioned I am very new to macros and programming in excel so it did not make much sense.

I have tried to follow the instructions in the article as I am sure this will ultimately help get what I need done but I have run into the following problems:
1. The article says that I should select my graph by clicking on it and then selecting the view code option, there I must select the "Chart" option in the drop down, but when I do this in my workbook the only options I have are "(General)" and "Worksheet".
2. I also noticed that article says I should click on my chart and press F11, I am not sure what this actually does apart from opening a new worksheet with a graph on it. When I tried this on my worksheet the graph appears blank, so I tried to do this with a new worksheet using the example in the article and when press F11 the chart appears in a new worksheet.
3. It appears that once I press F11 the chart is some how activated but I am just looking to use the chart in its current place as I have setup the sheet in such away that the cells behind act as matrix for the graph.

I do apologise for asking such basic questions but I am trying to get this to work.

Again thanks for the assistance thus far

Originally Posted by Andy Pope

The following will display a message box when you click series or data point.

Thisworkbook module

Private Sub Workbook_Open()

    Main
    
End Sub

Module1 code

Option Explicit

Private m_clsChartEvents As Class1
Sub Main()

    Set m_clsChartEvents = New Class1
    Set m_clsChartEvents.Cht = Worksheets("Main").ChartObjects(1).Chart
    
End Sub

Class1 code

Option Explicit

Public WithEvents Cht As Chart
Private Sub Cht_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)

    Dim vntData As Variant
    Dim vntXData As Variant
    
    If ElementID = 3 Then
        ' selected series
        If Arg2 = -1 Then
            ' selected whole series
            MsgBox "Selected Series [" & Arg1 & "] " & Cht.SeriesCollection(Arg1).Name, vbInformation, "Series"
        Else
            ' selected data point
            vntData = Cht.SeriesCollection(Arg1).Values
            vntXData = Cht.SeriesCollection(Arg1).XValues
            MsgBox "Selected DataPoint " & Arg2 & " from Series [" & Arg1 & "] " & Cht.SeriesCollection(Arg1).Name & vbLf & _
                   vntXData(Arg2) & "," & vntData(Arg2), vbInformation, "Series"
        End If
    End If
    
End Sub

Hi Andy

Thank you so much for all your assistance it is working perfectly, I am not sure if I still fully understand it but I am sure I will get there.

I made some minor modifications to your code:


Private Sub Cht_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)

Dim vntData As Variant
Dim vntXData As Variant

If ElementID = 3 Then
' selected series
If Arg2 = -1 Then
' selected whole series
'MsgBox "Selected Series [" & Arg1 & "] " & Cht.SeriesCollection(Arg1).Name, vbInformation, "Series"
If Arg1 = "3" Then
Sheets("Worksheet 1").Select
Else
If Arg1 = "4" Then
Sheets("Worksheet 2").Select
Else
If Arg1 = "5" Then
Sheets("Worksheet 3").Select
Else
If Arg1 = "6" Then
Sheets("Worksheet 4").Select
Else
If Arg1 = "7" Then
Sheets("Worksheet 5").Select
Else
If Arg1 = "8" Then
Sheets("Worksheet 6").Select
Else
If Arg1 = "9" Then
Sheets("Worksheet 7").Select
Else
If Arg1 = "10" Then
Sheets("Worksheet 8").Select
Else
If Arg1 = "11" Then
Sheets("Worksheet 9").Select
Else
If Arg1 = "12" Then
Sheets("Worksheet 10").Select
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Else
' selected data point
vntData = Cht.SeriesCollection(Arg1).Values
vntXData = Cht.SeriesCollection(Arg1).XValues
'MsgBox "Selected DataPoint " & Arg2 & " from Series [" & Arg1 & "] " & Cht.SeriesCollection(Arg1).Name & vbLf & _
vntXData(Arg2) & "," & vntData(Arg2), vbInformation, "Series"
' Sheets("worksheet 1").Select

End If
End If

End Sub

I am sure there is a more eloquent way of doing this but it works.

Thanks again