Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populating the Chart Gallery (Excel 2003)

    Can anyone help?

    I am creating a toolbar in Excel which I would like to populate with the Chart Gallery. Do you know of a way of accessing the Chart Gallery through VBA? I just want to pick up the name of the chart and the FaceId.

    Many thanks
    Jack

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating the Chart Gallery (Excel 2003)

    The Chart Type toolbar is populated on the fly, as far as I know. According to VBA, it has only one control.
    Here is a table listing the available custom toolbar buttons for chart types:

    <table border=1><td>Chart type</td><td>FaceID</td><td>Area Chart</td><td align=right>418</td><td>Bar Chart</td><td align=right>419</td><td>Column Chart</td><td align=right>420</td><td>Stacked Column Chart</td><td align=right>421</td><td>Line Chart</td><td align=right>422</td><td>Pie Chart</td><td align=right>423</td><td>3-D Area Chart</td><td align=right>424</td><td>3-D Bar Chart</td><td align=right>425</td><td>3-D Clustered Column Chart</td><td align=right>426</td><td>3-D Column Chart</td><td align=right>427</td><td>3-D Line Chart</td><td align=right>428</td><td>3-D Pie Chart</td><td align=right>429</td><td>(XY) Scatter Chart</td><td align=right>430</td><td>Bubble Chart</td><td align=right>1635</td><td>3-D Surface Chart</td><td align=right>431</td><td>3-D Cylinder Chart</td><td align=right>1636</td><td>3-D Cone Chart</td><td align=right>1638</td><td>3-D Pyramid Chart</td><td align=right>1637</td><td>Radar Chart</td><td align=right>432</td><td>Volume/High-Low-Close Chart</td><td align=right>434</td><td>Open-High-Low-Close Chart</td><td align=right>1844</td><td>Doughnut Chart</td><td align=right>449</td></table>

  3. #3
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating the Chart Gallery (Excel 2003)

    Thanks Hans.

    Just for clarification, are you saying that it is not possible to populate the list as there is only one control which is "Chart Type"? Does this mean that I would have to hard code the chart types and FaceID numbers?

    Thanks as always
    Jack

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating the Chart Gallery (Excel 2003)

    If you just want the same control with the same functionality, use this:

    <pre>Sub demo()
    Dim oBar As CommandBar
    On Error Resume Next
    Application.CommandBars("test").Delete
    With Application.CommandBars.Add("test")
    .Controls.Add Type:=msoControlSplitButtonPopup, ID:=918
    .Visible = True
    End With
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating the Chart Gallery (Excel 2003)

    I probably don't understand what you want. Do you want to place the Chart Type dropdown on your toolbar? You can do this as follows:

    CommandBars("My Toolbar").Controls.Add ID:=918

    If you want to individual items for each chart type, you'd have to create each of them separately, using the IDs from my previous reply.

    Or do you want something else?

  6. #6
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating the Chart Gallery (Excel 2003)

    Hi Hans .... apologies for not being clear.

    I want to populate a toolbar using a loop which identifies all the chart types, something on the lines of

    For lngControls = 1 to CommandBar("Chart Type").Controls.Count
    THEN SET UP COMMAND BUTTON USING THE NAME OF THE CHART AND THE FACEID
    next lngControls

    As there are a number of chart types I did not want to physically type in the chart name and FaceID of every single one. My preference was to cycle through the collection and populate the toolbar.

    Thanks
    Jack

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating the Chart Gallery (Excel 2003)

    Even though the Chart Type toolbar contains 18 buttons or so, CommandBar("Chart Type").Controls.Count evaluates to 1. So looping through the controls of the Chart Type toolbar won't work. I fear you'll have to hard-code the names and FaceIDs.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating the Chart Gallery (Excel 2003)

    I had cycled through the Chart Type command bar and realised that it was only recognising one control, just thought there might be a way around it ... but obviously not.

    Thanks for your help anyway
    Jack

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •