locked
Identify which data series is selected and which not in PowerPoint 2007 chart using VBA. RRS feed

  • Question

  • Hi,

     

    I have a Chart in PowerPoint 2007 which has multiple data series. I wanted to apply fill color to specific selected data series only using VBA.

     

    Is there any property available within

    activewindow.Selection.ShapeRange(1).Chart.SeriesCollection

    which will tell me which data series is selected and which not, so that I can apply Fill Color to that specific selected data series only?

     

    In advance appreciate your help and time.

     

    Regards,

    Ramesh

    Tuesday, March 8, 2011 8:50 PM

Answers

  • Module: Accessibility

    Option Explicit

    Public Const CHILDID_SELF                  As Long = &H0&

    Private Const STATE_SYSTEM_UNAVAILABLE     As Long = &H1&
    Private Const STATE_SYSTEM_INVISIBLE       As Long = &H8000&
    Private Const STATE_SYSTEM_SELECTED        As Long = &H2&

    Public Enum RoleNumber
        ROLE_SYSTEM_CLIENT = &HA&
        ROLE_SYSTEM_PANE = &H10&
        ROLE_SYSTEM_GROUPING = &H14&
        ROLE_SYSTEM_TOOLBAR = &H16&
        ROLE_SYSTEM_PROPERTYPAGE = &H26&
        ROLE_SYSTEM_GRAPHIC = &H28&
        ROLE_SYSTEM_STATICTEXT = &H29&
        ROLE_SYSTEM_Text = &H2A&
        ROLE_SYSTEM_PAGETABLIST = &H3C&
    End Enum

    Private Enum NavigationDirection
        NAVDIR_FIRSTCHILD = &H7&
    End Enum

    Private Declare Function AccessibleChildren _
                    Lib "oleacc.dll" _
                        (ByVal paccContainer As Object, _
                         ByVal iChildStart As Long, _
                         ByVal cChildren As Long, _
                               rgvarChildren As Variant, _
                               pcObtained As Long) _
                    As Long

    Private Declare Function GetRoleText _
                    Lib "oleacc.dll" _
                    Alias "GetRoleTextA" _
                        (ByVal dwRole As Long, _
                               lpszRole As Any, _
                         ByVal cchRoleMax As Long) _
                    As Long

    Public Type ChildList
        Objects()       As IAccessible
        Levels()        As Long
        SelectedIndex   As Long
    End Type
       
    Public Function GetAccessible _
                        (Element As IAccessible, _
                         RoleWanted As RoleNumber, _
                         NameWanted As String, _
                         Optional GetClient As Boolean) _
                    As IAccessible

        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
        ' This procedure recursively searches the accessibility hierarchy, starting '
        ' with the element given, for an object matching the given name and role.   '
        ' If requested, the Client object, assumed to be the first child, will be   '
        ' returned instead of its parent.                                           '
        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '

        Dim ChildrenArray()
        Dim Child               As IAccessible
        Dim ndxChild            As Long
        Dim ReturnElement       As IAccessible
       
        If Element.accRole(CHILDID_SELF) = RoleWanted _
        And Element.accName(CHILDID_SELF) = NameWanted Then

            Set ReturnElement = Element
           
        Else ' not found yet
       
            ChildrenArray = GetChildren(Element)
           
            If (Not ChildrenArray) <> True Then
               
                For ndxChild = LBound(ChildrenArray) To UBound(ChildrenArray)
                   
                    If TypeOf ChildrenArray(ndxChild) Is IAccessible Then
                   
                        Set Child = ChildrenArray(ndxChild)
                        Set ReturnElement = GetAccessible(Child, _
                                                          RoleWanted, _
                                                          NameWanted)
                        If Not ReturnElement Is Nothing Then Exit For
                   
                    End If ' Child is IAccessible
               
                Next ndxChild
           
            End If ' there are children
       
        End If ' still looking

        If GetClient Then
            Set ReturnElement = ReturnElement.accNavigate(NAVDIR_FIRSTCHILD, _
                                                          CHILDID_SELF)
        End If
       
        Set GetAccessible = ReturnElement
       
    End Function


    Public Function GetListOfChildren _
                        (Parent As IAccessible, _
                         Optional GetDescendents As Boolean = True) _
                    As ChildList

        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
        ' Given a parent IAccessible object, will return a (UDT ChildList) array of '
        ' its children. Each returned object will be the bottom one of a leg in the '
        ' Accessibility hierarchy, unless told not to look at children's children.  '
        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
       
        Dim ChildInfo               As ChildList
        Dim ndxChild                As Long
        Dim Child                   As IAccessible
       
        Dim LocalChildren()         As Variant
        Dim LocalAncestry()         As IAccessible
       
        Dim GrandChildInfo          As ChildList
        Dim ndxGrandChild           As Long
        Dim GrandChild              As IAccessible
       
        LocalChildren = GetChildren(Parent)

        If (Not LocalChildren) <> True Then
           
            For ndxChild = LBound(LocalChildren) To UBound(LocalChildren)
           
                Set Child = LocalChildren(ndxChild)
           
                If Child.accRole(CHILDID_SELF) <> ROLE_SYSTEM_GRAPHIC _
                And Child.accRole(CHILDID_SELF) <> ROLE_SYSTEM_STATICTEXT Then
               
                    If ((Child.accState(CHILDID_SELF) _
                        And (STATE_SYSTEM_UNAVAILABLE _
                             Or STATE_SYSTEM_INVISIBLE)) = 0) Then
       
                        If Child.accChildCount = 0 _
                        Or GetDescendents = False Then
                       
                            AddChildToList Child, ChildInfo
                       
                        Else
                           
                            GrandChildInfo = GetListOfChildren(Child)
                           
                            If (Not GrandChildInfo.Objects) <> True Then
                           
                                For ndxGrandChild = LBound(GrandChildInfo.Objects) _
                                                    To UBound(GrandChildInfo.Objects)
                               
                                    Set GrandChild _
                                        = GrandChildInfo.Objects(ndxGrandChild)
                                       
                                    AddChildToList GrandChild, ChildInfo
                                    ChildInfo.Levels(UBound(ChildInfo.Objects)) _
                                        = GrandChildInfo.Levels(ndxGrandChild) + 1
                                   
                                Next ndxGrandChild
                               
                            End If ' Any grandchildren found?
                           
                        End If ' Check for grandchildren?
       
                    End If ' Not unavailable
                   
                End If ' Not (graphic or text)

            Next ndxChild
       
        End If ' Any children?
       
        GetListOfChildren = ChildInfo
       
    End Function


    Private Sub AddChildToList _
                    (Child As IAccessible, _
                     ChildInfo As ChildList)
       
        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
        ' Adds an array entry and fills it with the passed IAccessible object. If   '
        ' the object is the currently selected one, the fact is recorded.           '
        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
       
        With ChildInfo
           
            If (Not .Objects) = True Then
                ReDim .Objects(0 To 0)
                ReDim .Levels(LBound(.Objects) To UBound(.Objects))
            Else
                ReDim Preserve .Objects(LBound(.Objects) To UBound(.Objects) + 1)
                ReDim Preserve .Levels(LBound(.Objects) To UBound(.Objects))
            End If
           
            Set .Objects(UBound(.Objects)) = Child
       
            If ((Child.accState(CHILDID_SELF) And (STATE_SYSTEM_SELECTED)) _
                                                 = STATE_SYSTEM_SELECTED) Then
                .SelectedIndex = UBound(.Objects)
            End If
           
        End With ' ChildInfo
       
    End Sub

    Private Function GetChildren _
                         (Element As IAccessible) _
                     As Variant()

        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
        ' General purpose subroutine to get an array of children of an IAccessible  '
        ' object. The returned array is Variant because the elements may be either  '
        ' IAccessible objects or simple (Long) elements, and the caller must treat  '
        ' them appropriately.                                                       '
        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
       
        Const FirstChild        As Long = 0&

        Dim NumChildren         As Long
        Dim NumReturned         As Long
       
        Dim ChildrenArray()

        NumChildren = Element.accChildCount

        If NumChildren > 0 Then
       
            ReDim ChildrenArray(NumChildren - 1)
            AccessibleChildren Element, FirstChild, NumChildren, _
                               ChildrenArray(0), NumReturned

        End If
       
        GetChildren = ChildrenArray
       
    End Function

     

    ======================================================================================

    Call Following function to get the Selected Chart Elements

    Private Function GetSelectedChartElements() As String
    'Remarks: This function will read the Chart Element dropdown and return selected element value

        Dim sChartElement As String
        Dim RibbonPropPage          As IAccessible
        Dim PageTabListClient       As IAccessible
        Dim RibbonPaneClient        As IAccessible
        Dim ActiveTabPropPage       As IAccessible
        Dim GroupToolBar            As IAccessible
        'Dim TabInfo                 As ChildList
        Dim ItemInfo                As ChildList
        Dim TabName                 As String
       
        On Error Resume Next
       
        '// Get accessibles for "Ribbon"
        Set RibbonPropPage = GetAccessible(CommandBars("Ribbon"), _
                                           ROLE_SYSTEM_PROPERTYPAGE, _
                                           "Ribbon")
                                          
        '// Based on Ribbon accessible, Get accessibles for "Ribbon Tabs"
        Set PageTabListClient = GetAccessible(RibbonPropPage, _
                                              ROLE_SYSTEM_PAGETABLIST, _
                                              "Ribbon Tabs", _
                                              True)
                                            
        '// Get all Tabs in Tabs child list
        TabInfo = GetListOfChildren(PageTabListClient)

        '// Look for "Format" tab and select it
        TabName = "Format"
        SelectTab TabName
       
        '// Based on Ribbon Tabs accessibles, Get accessibles fro "Lower Ribbon"
        Set RibbonPaneClient = GetAccessible(RibbonPropPage, _
                                             ROLE_SYSTEM_PANE, _
                                             "Lower Ribbon", _
                                             True)
                                            
        DoEvents
        '// Once you have Lower Ribbon accessibles, Get accessibles only for "Format"
        Set ActiveTabPropPage = GetAccessible(RibbonPaneClient, _
                                              ROLE_SYSTEM_PROPERTYPAGE, _
                                              TabName)
       
        '// Now finally Get accessibles for "Current Selection"
        Set GroupToolBar = GetAccessible(ActiveTabPropPage, _
                                         ROLE_SYSTEM_TOOLBAR, _
                                         "Current Selection")

        '// Collect itemes found in chile list collection
        ItemInfo = GetListOfChildren(GroupToolBar)
       
        '// Since we are just interested current selection, look for the 0th index value and return
        sChartElement = ItemInfo.Objects(0).accValue(CHILDID_SELF)


        '// Finally return the selected Chart Element value
        GetSelectedChartElements = sChartElement
    End Function

    • Proposed as answer by roland.v Tuesday, November 22, 2011 3:44 PM
    • Marked as answer by Bruce Song Wednesday, November 23, 2011 12:28 AM
    Thursday, November 3, 2011 10:05 PM

All replies

  • Hi Ra1,

    Thank you for posting and we are glad to help with you.

    After reading your post, I wrote the following VBA code snippet which can fill color to the specific data series:

       Dim chrt As Shape
       Dim sc As SeriesCollection
       Dim ser As Series
      
       ' get chart
       Set chrt = ActiveWindow.Selection.ShapeRange(1)
       ' get chart series
       Set seri = chrt.Chart.SeriesCollection(1)
       With seri.Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent2
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
        End With

    So, we needn't to select the specific data series, just get the series from SeriesCollection.

    I hope it can help you and feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 9, 2011 4:34 AM
  • Thanks Bruce, but this is not I was looking for. I know how to fill the color but what I was looking for, selectively fill the color. user is selecting only one perticular series and then applying our custom colors through our ApplyColor functionality.

    You can manually achive the same, if you click on specific seriers, for example series 2, then do right-click, format data series, select Fill and apply fill color. this way user can apply specific fill color to a specific selected data series. we are trying to automate this.

    Let me know if someone has any solution for this.

    Regards,

    Ramesh

    Wednesday, March 9, 2011 2:51 PM
  • Hi Ra1,

    >> I know how to fill the color but what I was looking for, selectively fill the color. user is selecting only one perticular series and then applying our custom colors through our ApplyColor functionality.

    Do you mean you want to select one specific series? If so, just use the following code snippet:

    Dim oneSeries As series

    Set seri = chrt.Chart.SeriesCollection(2)

    seri.Select  ' select the seconde series

    It can select the specific Series 2. If I have misunderstood you, please just let me know.

    I hope this can help you and feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 11, 2011 5:53 AM
  • No I don't want to programmatically select a specific data series but I wanted to programmatically check which data series user has selected.

    Lets say user wants to set fill color for a specific data series, what user does is, user select that specific data series, got to Formatt and Set Fill color. the fill color that default color pallet doesn't have our custom colors.

    so if user wants to set our custom color they can not. we have our own color pallet which user selects but now since we don't know which data series user has selected we can not apply our custom color to that perticular selected data series.

    So far what I have found is that, I can use "GetChartElement" API to get the chart element. this API requires X and Y coordinates of the selected data series. unfortunatly PowerPoint doesn't have WithEvents for Chart object and hence I cannot trap MouseDown button which is possible in Excel but not in PowerPoint.

    In Excel if I trap Chart events I can get exact X and Y coordinates but in PowerPoint its difficult.

    I tried GetCursorPos API to get the X and Y coordinates for the selected data series but it doesn't.

    So I am still having an issue how to get X and Y.

    BTW I am using global hook to trap the mouse down event only on Chart using following API "SetWindowsHookEx" in PowerPoint.

    Regards,

    Ramesh

    Friday, March 11, 2011 4:03 PM
  • In case some one is interested, I have found a way to read the "Chart Elements" dropdown selected value using "AccessibleChildren" and "IAccessible" APIs.

    I will post the working version as soon as its ready.

    thanks for your help.

    Ramesh

    • Proposed as answer by Bruce Song Wednesday, March 16, 2011 3:38 AM
    Monday, March 14, 2011 10:21 PM
  • Sorry - I don't have an answer. But We have exactly the same Problem. We want to assign a corporate-design-colour to a user-selected chart-part to chart that is embedded in a word-document. When it works in word, we have to solve the same problem in powerpoint also.

    Although I find it's a good idea to figure out the coordinates where the user has clicked - I don't believe it's helpful. Even if you know where the user has clicked, you don't know exactly what he means. In case of a chart it could be a single data point (on bar in a bar-chart) or the whole series (al bars in the series in a bar chart).

    If somebody has an idea - we'd be glad to hear about.

    @ra1 - colud you find a working idea.

    Thanks for any help
    Roland

    Monday, March 28, 2011 4:18 PM
  • thanks Roland. Yes we do have now working solution. If you are interested, send me your emailID and I will send you the working solution.

    Thanks,

    Ramesh

    • Proposed as answer by Bruce Song Monday, April 11, 2011 9:21 AM
    Wednesday, April 6, 2011 2:46 PM
  • Hallo RA1.

    Sorry for the delayed answer but I was on course and didn't check my mailbox in the last few days.

    Sure I am very interested in your solution. But isn't it possible to post the solution here in the forum instead of sending it by mail. I prefer that way for two reasons.

    1. I think other members could also be interested in the answer.

    2. To avoid spam, I don't like posting my Mail-Address in Forums or is there a way to give you my mail-address without showing it to every one?

    Thanks Roland

    Monday, April 11, 2011 10:50 AM
  • Hallo Ramesh (RA1)

    I was waiting for your answer in the forum. It'seems that you perfer sendig it by mail. So here is my mailaddress roland(dot)vogler(at)llb(dot)li

    Thanks
    Roland


    Monday, April 18, 2011 12:41 PM
  • Module: Accessibility

    Option Explicit

    Public Const CHILDID_SELF                  As Long = &H0&

    Private Const STATE_SYSTEM_UNAVAILABLE     As Long = &H1&
    Private Const STATE_SYSTEM_INVISIBLE       As Long = &H8000&
    Private Const STATE_SYSTEM_SELECTED        As Long = &H2&

    Public Enum RoleNumber
        ROLE_SYSTEM_CLIENT = &HA&
        ROLE_SYSTEM_PANE = &H10&
        ROLE_SYSTEM_GROUPING = &H14&
        ROLE_SYSTEM_TOOLBAR = &H16&
        ROLE_SYSTEM_PROPERTYPAGE = &H26&
        ROLE_SYSTEM_GRAPHIC = &H28&
        ROLE_SYSTEM_STATICTEXT = &H29&
        ROLE_SYSTEM_Text = &H2A&
        ROLE_SYSTEM_PAGETABLIST = &H3C&
    End Enum

    Private Enum NavigationDirection
        NAVDIR_FIRSTCHILD = &H7&
    End Enum

    Private Declare Function AccessibleChildren _
                    Lib "oleacc.dll" _
                        (ByVal paccContainer As Object, _
                         ByVal iChildStart As Long, _
                         ByVal cChildren As Long, _
                               rgvarChildren As Variant, _
                               pcObtained As Long) _
                    As Long

    Private Declare Function GetRoleText _
                    Lib "oleacc.dll" _
                    Alias "GetRoleTextA" _
                        (ByVal dwRole As Long, _
                               lpszRole As Any, _
                         ByVal cchRoleMax As Long) _
                    As Long

    Public Type ChildList
        Objects()       As IAccessible
        Levels()        As Long
        SelectedIndex   As Long
    End Type
       
    Public Function GetAccessible _
                        (Element As IAccessible, _
                         RoleWanted As RoleNumber, _
                         NameWanted As String, _
                         Optional GetClient As Boolean) _
                    As IAccessible

        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
        ' This procedure recursively searches the accessibility hierarchy, starting '
        ' with the element given, for an object matching the given name and role.   '
        ' If requested, the Client object, assumed to be the first child, will be   '
        ' returned instead of its parent.                                           '
        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '

        Dim ChildrenArray()
        Dim Child               As IAccessible
        Dim ndxChild            As Long
        Dim ReturnElement       As IAccessible
       
        If Element.accRole(CHILDID_SELF) = RoleWanted _
        And Element.accName(CHILDID_SELF) = NameWanted Then

            Set ReturnElement = Element
           
        Else ' not found yet
       
            ChildrenArray = GetChildren(Element)
           
            If (Not ChildrenArray) <> True Then
               
                For ndxChild = LBound(ChildrenArray) To UBound(ChildrenArray)
                   
                    If TypeOf ChildrenArray(ndxChild) Is IAccessible Then
                   
                        Set Child = ChildrenArray(ndxChild)
                        Set ReturnElement = GetAccessible(Child, _
                                                          RoleWanted, _
                                                          NameWanted)
                        If Not ReturnElement Is Nothing Then Exit For
                   
                    End If ' Child is IAccessible
               
                Next ndxChild
           
            End If ' there are children
       
        End If ' still looking

        If GetClient Then
            Set ReturnElement = ReturnElement.accNavigate(NAVDIR_FIRSTCHILD, _
                                                          CHILDID_SELF)
        End If
       
        Set GetAccessible = ReturnElement
       
    End Function


    Public Function GetListOfChildren _
                        (Parent As IAccessible, _
                         Optional GetDescendents As Boolean = True) _
                    As ChildList

        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
        ' Given a parent IAccessible object, will return a (UDT ChildList) array of '
        ' its children. Each returned object will be the bottom one of a leg in the '
        ' Accessibility hierarchy, unless told not to look at children's children.  '
        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
       
        Dim ChildInfo               As ChildList
        Dim ndxChild                As Long
        Dim Child                   As IAccessible
       
        Dim LocalChildren()         As Variant
        Dim LocalAncestry()         As IAccessible
       
        Dim GrandChildInfo          As ChildList
        Dim ndxGrandChild           As Long
        Dim GrandChild              As IAccessible
       
        LocalChildren = GetChildren(Parent)

        If (Not LocalChildren) <> True Then
           
            For ndxChild = LBound(LocalChildren) To UBound(LocalChildren)
           
                Set Child = LocalChildren(ndxChild)
           
                If Child.accRole(CHILDID_SELF) <> ROLE_SYSTEM_GRAPHIC _
                And Child.accRole(CHILDID_SELF) <> ROLE_SYSTEM_STATICTEXT Then
               
                    If ((Child.accState(CHILDID_SELF) _
                        And (STATE_SYSTEM_UNAVAILABLE _
                             Or STATE_SYSTEM_INVISIBLE)) = 0) Then
       
                        If Child.accChildCount = 0 _
                        Or GetDescendents = False Then
                       
                            AddChildToList Child, ChildInfo
                       
                        Else
                           
                            GrandChildInfo = GetListOfChildren(Child)
                           
                            If (Not GrandChildInfo.Objects) <> True Then
                           
                                For ndxGrandChild = LBound(GrandChildInfo.Objects) _
                                                    To UBound(GrandChildInfo.Objects)
                               
                                    Set GrandChild _
                                        = GrandChildInfo.Objects(ndxGrandChild)
                                       
                                    AddChildToList GrandChild, ChildInfo
                                    ChildInfo.Levels(UBound(ChildInfo.Objects)) _
                                        = GrandChildInfo.Levels(ndxGrandChild) + 1
                                   
                                Next ndxGrandChild
                               
                            End If ' Any grandchildren found?
                           
                        End If ' Check for grandchildren?
       
                    End If ' Not unavailable
                   
                End If ' Not (graphic or text)

            Next ndxChild
       
        End If ' Any children?
       
        GetListOfChildren = ChildInfo
       
    End Function


    Private Sub AddChildToList _
                    (Child As IAccessible, _
                     ChildInfo As ChildList)
       
        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
        ' Adds an array entry and fills it with the passed IAccessible object. If   '
        ' the object is the currently selected one, the fact is recorded.           '
        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
       
        With ChildInfo
           
            If (Not .Objects) = True Then
                ReDim .Objects(0 To 0)
                ReDim .Levels(LBound(.Objects) To UBound(.Objects))
            Else
                ReDim Preserve .Objects(LBound(.Objects) To UBound(.Objects) + 1)
                ReDim Preserve .Levels(LBound(.Objects) To UBound(.Objects))
            End If
           
            Set .Objects(UBound(.Objects)) = Child
       
            If ((Child.accState(CHILDID_SELF) And (STATE_SYSTEM_SELECTED)) _
                                                 = STATE_SYSTEM_SELECTED) Then
                .SelectedIndex = UBound(.Objects)
            End If
           
        End With ' ChildInfo
       
    End Sub

    Private Function GetChildren _
                         (Element As IAccessible) _
                     As Variant()

        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
        ' General purpose subroutine to get an array of children of an IAccessible  '
        ' object. The returned array is Variant because the elements may be either  '
        ' IAccessible objects or simple (Long) elements, and the caller must treat  '
        ' them appropriately.                                                       '
        ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
       
        Const FirstChild        As Long = 0&

        Dim NumChildren         As Long
        Dim NumReturned         As Long
       
        Dim ChildrenArray()

        NumChildren = Element.accChildCount

        If NumChildren > 0 Then
       
            ReDim ChildrenArray(NumChildren - 1)
            AccessibleChildren Element, FirstChild, NumChildren, _
                               ChildrenArray(0), NumReturned

        End If
       
        GetChildren = ChildrenArray
       
    End Function

     

    ======================================================================================

    Call Following function to get the Selected Chart Elements

    Private Function GetSelectedChartElements() As String
    'Remarks: This function will read the Chart Element dropdown and return selected element value

        Dim sChartElement As String
        Dim RibbonPropPage          As IAccessible
        Dim PageTabListClient       As IAccessible
        Dim RibbonPaneClient        As IAccessible
        Dim ActiveTabPropPage       As IAccessible
        Dim GroupToolBar            As IAccessible
        'Dim TabInfo                 As ChildList
        Dim ItemInfo                As ChildList
        Dim TabName                 As String
       
        On Error Resume Next
       
        '// Get accessibles for "Ribbon"
        Set RibbonPropPage = GetAccessible(CommandBars("Ribbon"), _
                                           ROLE_SYSTEM_PROPERTYPAGE, _
                                           "Ribbon")
                                          
        '// Based on Ribbon accessible, Get accessibles for "Ribbon Tabs"
        Set PageTabListClient = GetAccessible(RibbonPropPage, _
                                              ROLE_SYSTEM_PAGETABLIST, _
                                              "Ribbon Tabs", _
                                              True)
                                            
        '// Get all Tabs in Tabs child list
        TabInfo = GetListOfChildren(PageTabListClient)

        '// Look for "Format" tab and select it
        TabName = "Format"
        SelectTab TabName
       
        '// Based on Ribbon Tabs accessibles, Get accessibles fro "Lower Ribbon"
        Set RibbonPaneClient = GetAccessible(RibbonPropPage, _
                                             ROLE_SYSTEM_PANE, _
                                             "Lower Ribbon", _
                                             True)
                                            
        DoEvents
        '// Once you have Lower Ribbon accessibles, Get accessibles only for "Format"
        Set ActiveTabPropPage = GetAccessible(RibbonPaneClient, _
                                              ROLE_SYSTEM_PROPERTYPAGE, _
                                              TabName)
       
        '// Now finally Get accessibles for "Current Selection"
        Set GroupToolBar = GetAccessible(ActiveTabPropPage, _
                                         ROLE_SYSTEM_TOOLBAR, _
                                         "Current Selection")

        '// Collect itemes found in chile list collection
        ItemInfo = GetListOfChildren(GroupToolBar)
       
        '// Since we are just interested current selection, look for the 0th index value and return
        sChartElement = ItemInfo.Objects(0).accValue(CHILDID_SELF)


        '// Finally return the selected Chart Element value
        GetSelectedChartElements = sChartElement
    End Function

    • Proposed as answer by roland.v Tuesday, November 22, 2011 3:44 PM
    • Marked as answer by Bruce Song Wednesday, November 23, 2011 12:28 AM
    Thursday, November 3, 2011 10:05 PM
  • Hallo Ramsesh (RA1)

    Thanks for your solution. It's a good Idea to read the information from the ribbon. We will test it as soon as possible.

    Meanwhile many thanks
    Roland

    Tuesday, November 22, 2011 3:43 PM
  • Hi Ramesh,

    I'd like to adopt your idea to C#, but having problems in understanding what the constants' values are (like &H0&).

    Could you help me out here?

    Thanks,
    Thomas

    Saturday, August 31, 2013 1:21 PM
  • Hi Ra1,

    That's really a great idea to grab the informatio from the Ribbon. But the string we get in this dropdown is plain text and localized. How can we extract the index of series and point? Thanks a lot.

    Jeff

    Thursday, May 8, 2014 8:17 PM
  • Hi Jeff,

    I think the best you could do is to parse the string that is obtained using the above method and extract the series name and datapoint value from it.

    example: if the above string returns a value Series "Series 1" Point "2004"

    "Series 1" is your series name and "2004" is your datapoint value.

    You might have to loop through the SeriesCollection in order to find its respective index

    Friday, May 23, 2014 7:55 PM
  • Hello Ramesh,

    I tried to get your Solution to work, but VBA fails to find the "SelectTab" function. Am I missing something? Anyone else tried it in Office 2010?

    Regards

    Julian

    Friday, November 6, 2015 11:29 AM
  • Probably way to late, but the "&H" forces vba to interpret the value afterwards as Hex, so &H0& = 0, &H8000& = 32768
    Monday, November 9, 2015 8:42 AM