Help: Embedded chart event and Cells function

[yurble_vn]

Dear All,

I have followed Jon Peltier instruction (followed links) and use the Tushar-mehta built code to trap embedded chart event.
http://www.computorcompanion.com/LPMArticle.asp?ID=221
http://www.tushar-mehta.com/excel/software/chart_hover_label/index.html

And it is totally works.

But when I try to get some data in the active sheet from by Cells() function, it always return error:
Run-time error: '1004'
"Methos 'Cells' of Object '_Global' failed"

Is there anyway to get data in currentsheet when in Class?

Please help

Thanks
Below is the code for the Class that I'm trying edit from Tushar-mehta code:

' WhichPointSelected.xls

' Class Module to Enable Events for Embedded Chart
' When a chart is clicked on:
' If a point is under the cursor, the message box shows point information
' If anything else is under the cursor, the message box tells which ElementID it was
' But
' MouseUp doesn't always seem to work:
' It won't capture shapes
' It won't capture chart area of chart embedded in chart sheet
' MouseDown captures shapes, but
' If shape is regular drawing object, mouseup doesn't occur, so object is dragged
' If shape is embedded chart, two message boxes pop up
' ElementID 14 for shape, plus pop up for embedded chart

Option Explicit

' Declare object of type "Chart" with events
Public WithEvents EmbChart As Chart


' Event procedures for "Chart" object go here
' ++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub EmbChart_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 Double, myY As Double
Dim SeriesNameNo As Integer
Dim Sheetname As String
Dim SetCol(1 To 10) As Integer
Dim icount As Integer
Const DataLabelLine As Integer = 28

Sheetname = ActiveSheet.Name

MsgBox Sheetname
For icount = 1 To 10
SetCol(icount) = Cells(icount + 5, "AB") ' <--------------Error Here
Next

If Button = xlPrimaryButton Then
With EmbChart
.GetChartElement X, Y, ElementID, Arg1, Arg2

Application.StatusBar = "[" & ElementID & "]"

If ElementID = xlSeries Or ElementID = xlDataLabel Then
If Arg2 > 0 Then
myX = WorksheetFunction.Index(.SeriesCollection(Arg1).XValues, Arg2)
myY = WorksheetFunction.Index(.SeriesCollection(Arg1).Values, Arg2)
Application.StatusBar = "[" & myX & ", " & myY & "]"
SeriesNameNo = VBA.Right(.SeriesCollection(Arg1).Name, 1)

MsgBox "Type " & Arg1 & vbCrLf _
& Arg2 & vbCrLf _
& "X = " & myX
End If
End If

End With
End If
Application.StatusBar = False
End Sub

[Bob Phillips]

Do you have a chart sheet active? Maybe qualify Cells with the worksheet object that they reside upon.

[yurble_vn]

Thanks for prompt feedback XLD,

I have tried with

For icount = 1 To 10
SetCol(icount) = ActiveSheet.Cells(icount + 5, "AB") ' <--------------Error Here
Next
But there is still error.

I'm wrong in somewhere?

[Bob Phillips]

That will not work if the activesheet is a chart. I meant qualify with the actual sheet object.

[yurble_vn]

Sorry for any non-sense statement I potentially say as I just amateur in VBA:

For your information, the below code works well
SetCol(1) = ActiveSheet.Range("AB6")
And the I just use chart embedded in sheet, not seperated sheet.

But whenever I replace the "AB6" by a available, it wont works

I also attached the file for your reference.

When open the file, you can see what I going to do is: whenever the plot point is click, the macro will go back to the sheet and get the description associated with that plot point

Thanks

[yurble_vn]

Sorry In case I miss, but what do you mean by saying "qualify" with the actual sheet object

Thanks

[Bob Phillips]

I don't think that is te problem as you don't have a chart sheet.

So what is the problem? It outputs the point details for me.

BTW I recognise this chart, I am sure it is the one I 'stole' in an earlier incarnation and presented it at a seminar as an example of a well constructed chart.

[yurble_vn]

The chart works well because I have deleted error code. (transfer into comment)

I just try to develop from my earlier version which also built with loads of aid from VBAepress memeber.

What I try to do is that: when I click on one point, it will return the description of that point, not only the information of that point. And the description is got from the sheet (as it can not be stored in chart properties)

So, I use the point order. First, I get the point order from chart property, then, I go back to the chart data range and get the description of that point.

My trouble here is that: I can not get the description from the sheet through cells() function. it alway return error.

[Bob Phillips]

Okay I get the general idea, but I don't get the detail. The data you MsgBox seems to be a date and some number. How do I translate this into te description?

[yurble_vn]

Until now, the Msgbox is just information (properties) of the plot point:
-Series Number (which commence "Type").
-Point order
-X value of that point (the date)

My next step (and get stuck) is: from the Series Number , I go back to the data of that Series. Then from the Point Order, I navigate the row of that point and get the description.

And that is where I get stuck.
1.Because the series Data Range Position is save in the sheet (AB6:AB15). So I have to get it to navigate the data range.
2. After that from that information, I have to get the description from the sheet.

And all these steps, I think, have to use Cells() function or some kinds of function to get data from the active sheet (and can be call from the embedded chart class).

[yurble_vn]

should you need any further explaination?
Thanks

[Bob Phillips]

Yes please.

Give me an example of a datapoint, what information you do get, where it is, and what information you want, and where it is, that is sheet/cell references.

[yurble_vn]

Please find attached new version with explaination.
Hope it clear enough.

[yurble_vn]

Is that clear enough?
Thanks advance for all.

[JonPeltier]

For icount = 1 To 10
SetCol(icount) = ActiveSheet.Cells(icount + 5, "AB") ' <--------------Error Here
Next
But there is still error.


If the active sheet is a worksheet, you could try to use the Range object instead of Cells, and use the .Value property of the resulting range:



SetCol(icount) = ActiveSheet.Range("AB" & CStr(icount + 5)).Value

[yurble_vn]

THanks Jon,

It really works.