GetChartElement Errors, Constant Reverse Enumeration & Chart Decomposition

[pbornemeier]

The code below may be placed in a Chart codepage. It uses the Status Bar to display information about the part of the graph that is clicked. Moving the mouse clears the status bar.

Is there a programatic way to access reverse enumeration of Excel constants, given the area under consideration? For instance GetChartElement returns ElementID values. When it returns a 15, I can use the XlChartItem Enumeration table in VBA Help to determine this is xlMajorGridlines (and therby infer I clicked on a Major Gridline). I could not find a way to return the text value from the object model so in the code below I created arrays that hold the enumeration text being used. There should be a better way.

GetChartElement (apparent) errors

1) When a series is first clicked all lines are selected. For a line graph Arg2(PointIndex) returns the point AFTER the place where the line was clicked, rather than -1 for all points selected as outlined in the documentation.

2) Display Units Label returns 17 (xlAxisTitle) not 30 (xlDisplayUnitLabel) [Tool Tip shows … Units Label]

3) If the Data Table is visible the xlDataTable(7) is returned only when clicking on a number or line within the table, otherwise it returns xlChartArea(2)

4) If the Data Table is visible the the Primary Value axis numbers return xlDataTable(7) instead of xlAxis(21) (NOTE: Top row of DataTable is same as X-Axis) [Tool Tip shows Data Table]

5) Selecting an added shape in the chart returns xlNothing(28) rather than the xlShape(14) expected

6) The legend keys are visible in a Data Table, but the code shows xlDataTable(7) not xlLegendKey(13) [Tool Tip shows Data Table]

7) Can 't get anything to return items 6,13,14,30,31,32. I believe 31 & 31 are for older versions of Excel that could include Data Buttons on the graph.

If anyone can clear or confirm up my observations above, please update this thread.

Private Sub Chart_Mousedown(ByVal Button As Long, ByVal Shift As Long, _
    ByVal X As Long, ByVal Y As Long)

    Dim ElementID As Long
    Dim Arg1 As Long
    Dim Arg2 As Long
    Dim aryElementID As Variant
    Dim aryXlAxisGroup As Variant
    Dim aryXlCategory  As Variant
    Dim aryXlPivotFieldOrientation As Variant
    Dim sArg1 As String
    Dim sArg2 As String
    
    aryElementID = Array("xlDataLabel", "<no value>", "xlChartArea", "xlSeries", "xlChartTitle", _
        "xlWalls", "xlCorners", "xlDataTable", "xlTrendline", "xlErrorBars", "xlXErrorBars", _
        "xlYErrorBars", "xlLegendEntry", "xlLegendKey", "xlShape", "xlMajorGridlines", _
        "xlMinorGridlines", "xlAxisTitle", "xlUpBars", "xlPlotArea", "xlDownBars", _
        "xlAxis", "xlSeriesLines", "xlFloor", "xlLegend", "xlHiLoLines", "xlDropLines", _
        "xlRadarAxisLabels", "xlNothing", "xlLeaderLines", "xlDisplayUnitLabel", _
        "xlPivotChartFieldButton", "xlPivotChartDropZone")
    aryXlAxisGroup = Array("<No Value>", "xlPrimary", "xlSecondary")
    aryXlAxistype = Array("<No Value>", "xlCategory", "xlValue", "xlSeriesAxis")
    aryXlPivotFieldOrientation = Array("<No Value>", "xlRowField", "xlPageField", "xlDataField", "xlColumnField")
    
    With ActiveChart
        .GetChartElement X, Y, ElementID, Arg1, Arg2
        
        Select Case ElementID
        Case 15, 16, 17, 21, 30                 'AxisIndex/AxisType
            sArg1 = "Axis Location=" & aryXlAxisGroup(Arg1)
            sArg2 = "Axis Type=" & aryXlAxistype(Arg2)
        Case 32                                 'DropZoneType/None
            sArg1 = "DropZoneType"
        Case 31                                 'DropZoneType/PivotFieldIndex
            sArg1 = "DropZoneType"
            sArg2 = "Pivot Field Orientation=" & aryXlPivotFieldOrientation(Arg2)
        Case 18, 20, 22, 25, 26, 27             'GroupIndex/None
            sArg1 = "GroupIndex #"
        Case 9, 10, 11, 12, 13                  'SeriesIndex/None
            sArg1 = "Series #"
        Case 0, 3                               'SeriesIndex/PointIndex
            sArg1 = "Series #"
            sArg2 = "Point #"
            If Arg2 = -1 Then sArg2 = "All Points"
        Case 8                                  'SeriesIndex/TrendLineIndex
            sArg1 = "Series #"
            sArg2 = "TrendLineIndex #"
        Case 2, 4, 5, 6, 7, 19, 23, 24, 28, 29  'None/None
            'Arg1/Arg2 not returned
        Case 14                                 'ShapeIndex/None
            sArg1 = "ShapeIndex #"
        End Select
        
        'Dropped message box since the chart elements would often move as cursor moved to OK msgbox
        'MsgBox "X=" & X & vbLf & _
                "Y=" & Y & vbLf & _
                "Element ID=" & aryElementID(ElementID) & " (" & ElementID & ")" & vbLf & _
                IIf(Arg1 > 0, "Arg1=" & sArg1 & "(" & Arg1 & ")", "") & vbLf & _
                IIf(Arg2 > 0, "Arg2=" & sArg2 & "(" & Arg2 & ")", ""), , _
                "Chart Element"
                
        Application.StatusBar = "(X,Y)=(" & X & "," & Y & _
            "); Element ID=" & aryElementID(ElementID) & "(" & ElementID & ")" & _
            IIf(Arg1 > 0 Or Arg1 = -1, "; Arg1=" & sArg1 & "(" & Arg1 & ")", "") & _
            IIf(Arg2 > 0 Or Arg2 = -1, "; Arg2=" & sArg2 & "(" & Arg2 & ")", "")
            
    End With

End Sub

Private Sub Chart_MouseMove(ByVal Button As Long, _
        ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)
    Application.StatusBar = False
End Sub


'http://msdn.microsoft.com/en-us/library/office/aa195740(v=office.11).aspx
'Online table is incorrect: xlDataLabel constant value is 0, not 7

'Corrected Tab Separated ChartElement Data Table

'ElementID   Value   Arg1    Arg2
'xlMajorGridlines    15  AxisIndex   AxisType
'xlMinorGridlines    16  AxisIndex   AxisType
'xlAxisTitle 17  AxisIndex   AxisType
'xlAxis  21  AxisIndex   AxisType
'xlDisplayUnitLabel  30  AxisIndex   AxisType
'xlPivotChartDropZone    32  DropZoneType    None
'xlPivotChartFieldButton 31  DropZoneType    PivotFieldIndex
'xlUpBars    18  GroupIndex  None
'xlDownBars  20  GroupIndex  None
'xlSeriesLines   22  GroupIndex  None
'xlHiLoLines 25  GroupIndex  None
'xlDropLines 26  GroupIndex  None
'xlRadarAxisLabels   27  GroupIndex  None
'xlChartArea 2   None    None
'xlChartTitle    4   None    None
'xlWalls 5   None    None
'xlCorners   6   None    None
'xlDataTable 7   None    None
'xlPlotArea  19  None    None
'xlFloor 23  None    None
'xlLegend    24  None    None
'xlNothing   28  None    None
'xlLeaderLines   29  None    None
'xlErrorBars 9   SeriesIndex None
'xlXErrorBars    10  SeriesIndex None
'xlYErrorBars    11  SeriesIndex None
'xlLegendEntry   12  SeriesIndex None
'xlLegendKey 13  SeriesIndex None
'xlDataLabel 0   SeriesIndex PointIndex
'xlSeries    3   SeriesIndex PointIndex
'xlTrendline 8   SeriesIndex TrendLineIndex
'xlShape 14  ShapeIndex  None