Excel 2010 - Can't assign macro to part of a Column Chart (Data Point) 1

[PWD]

Good morning, I was hoping that I could assign a macro to a Data Point in a Column Chart so that the user could then see additional narrative that goes with the figures - just something simple like selecting another worksheet actually; unfortunately I only get the Series context shortcuts when I right click. Am I asking too much?

I have been pointed towards 'GetChartElement' by Skip but think I may have lost my way. I'm trying to use point index to determine the co-ordinates of the object I really want to act as a macro button but can't find a definitive list of their meanings only,

"point_index depends on the item we're looking at, but is a number from 1 to 8 to identify a specific vertex within the item. For example, 2 is the upper middle of any rectangular item, such as a column in a column chart."

Which is all very well and good but I can't be sure I'm using the right ones.

Anyway, I've found some more code that gets me "some" x & y co-ordinates for my object but when I proceed further I get,

Run-time error '438':
Object doesn't support this property or method

Sub test2()

  Dim dXVal As Double
  Dim dYVal As Double

  'We have to activate the chart to use GET.CHART.ITEM
  ActiveSheet.ChartObjects("Chart 14").Activate

  dXVal = ExecuteExcel4Macro("GET.CHART.ITEM(1,1,""S2P6"")") '= 425.25
  dYVal = ExecuteExcel4Macro("GET.CHART.ITEM(2,4,""S2P6"")") '= 124.5

ActiveSheet.ChartObjects("Chart 14").SeriesCollection(2).Points(6).GetChartElement dXVal, dYVal, xlNothing

End Sub

And even if it didn't error, I'm not really sure how far this would get me!!

Thoughts gratefully received.

Many thanks,
D€$

 

[SkipVought]

Is using the MouseDown event the only change?

 

[PWD]

Yes, that's the only change. I've now saved to the network drive the original uncoded file I'd sent out; added this code to it and it seems to be fine. Thanls for your help; something else I've added to my repetoire.

Just one final thing. I'd like to be able to hide the data worksheets and unhide then rehide when the Chart sheet is selected again. I've got the unhide part within the MouseDown code and was thinking about putting something in

Private Sub Chart_Activate()

Perhaps I'll try to hide everything that's not the chart sheet.

Many thanks,
D€$

 

[SkipVought]

I'd use a WORKBOOK SheetDeactivate event to HIDE the sheets in a list.

 

[SkipVought]

Let me clarify. I'd make a list or have the sheet names conform to a convention that you can logically applet to the Sh object reference in the Deactivate event. So if the Sh.Name is in the list or conforms to the convention, the set the Sh.Vibible property to xlVeryHidden.

 

[PWD]

Hi Skip, once again you've managed to dazzle me with your brilliance; you've lost me again.

What I've done is:

For Each ws In ActiveWorkbook.Worksheets

    If ws.Name <> "Chart1" Then
    ws.Visible = xlVeryHidden
    End If

Next ws

Many thanks,
D€$

 

[SkipVought]

I was referring to the Workbook_Deactivate event...

    If Sh.Name <> "Chart1" Then
       Sh.Visible = xlVeryHidden
    End If