How to assign a macro to a data point on chart

[joeyj]

Good Day, I really hope someone will be able to help me.

I am a nubee to macros and vb in excel and I would really appreciate the assistance.
I have attached a excel worksheet that has a graph that plots dots, what I need to be able to do is to be able to click on the dot (data series) and for excel to navigate to another worksheet.

The attached excel sheet has the graph on the first sheet and below it is the data it uses to plot.

For some reason excel does not allow me to assign a macro to dot (data series) only to the graph which does not help me.

Thanks
Joey

[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

[joeyj]

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

[Andy Pope]

1. The instructions explain how to create a chart sheet, by pressing F11. The view code for a chart sheet will include the chart object in the left dropdown and then relevant chart events in the right dropdown. This is not the case with chartobjects, those chart on a worksheet. For those you need to create a class to capture the chart events.

2. There are 2 mentions of F11. The first is in the instruction on how to create a chart sheet. The second is in conjustion with the ALT key in order to open the VBE.

3. I guessing that F11 is not activating the chart but instead inserting a new chart sheet.

[Andy Pope]

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

Thisworkbook module

Please Login or Register  to view this content.

Module1 code

Please Login or Register  to view this content.

Class1 code

Please Login or Register  to view this content.

[joeyj]

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

Thisworkbook module

Please Login or Register  to view this content.

Module1 code

Please Login or Register  to view this content.

Class1 code

Please Login or Register  to view this content.

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