none
Managing two charts on one chart sheet RRS feed

  • Question

  • In order to clearly show two data sets, I would like to have two charts on the same page.   One way to do this is with a trick explained in the Office 2007 Help and How-To ("Displaying multiple charts on a chart sheet" -- basically: make a blank chart page, then move charts onto it using the "Move Chart" function in the Design tab).

    My problem is that I am unable to access the charts in Visual Basic.  Although Excel sees that there is a chart page with the expected name, the SeriesCollection.Count for that chart is zero, even though the chart contains two charts that each have one series.  Perhaps when I moved the two charts to the empty chart page they suddenly became non-charts (or ex-charts), thereby preventing VB from accessing them.  Is this what happened, or is there another way to access the embedded charts?

    On a similar theme, if I put two charts onto a normal worksheet, I don't seem to be able to access the charts in a macro, as they aren't Charts and they aren't Sheets.

    Thanks in advance for your insights.

     

     

     

    Friday, July 29, 2011 10:27 PM

Answers

  • Embedded charts chan be created on a Chart sheet just as in a Worksheet, IOW a chart on a chart. Manually a little bit of "trickery" is involved, but with code none at all as the method is the same. Try this in a new workbook -

    [bvbnet]
    Sub test()
    Dim i As Long
    Dim L As Double, T As Double, W As Double, H As Double
    Dim chtSht As Chart
    Dim chtObj As ChartObject, cht As Chart
    Dim sr As Series, ax As Axis
    Dim rng As Range
        Set rng = ActiveWorkbook.Worksheets(1).Range("A1:D4")
       rng.Formula = "=int(rand()*10)"
        Set chtSht = ActiveWorkbook.Charts.Add
        chtSht.ChartObjects.Delete
       W = chtSht.ChartArea.Width / 2
       H = chtSht.ChartArea.Height / 2
        For i = 1 To 4
           L = (i - 1) Mod 2
           L = W * L
           If i >= 3 Then
               T = H
           End If
            Set chtObj = chtSht.ChartObjects.Add(L, T, W, H)
           Set cht = chtObj.Chart
            Set sr = cht.SeriesCollection.NewSeries
           sr.Values = rng.Columns(i)
           sr.Name = "data " & i
            cht.HasTitle = True
           cht.ChartTitle.Text = chtObj.Name
            Set ax = cht.Axes(xlValue)
           ax.MaximumScale = 10
        Next
        MsgBox "PRESS  F9  TO RECALC"
    End Sub
    [/vbnet]

    To re-reference the embedded charts, fully reference like this
    Chart-Sheet, ChartObject, Chart
    eg

    Set chtSht = ActiveWorkbook.Charts("Chart 1")
    Set chtObj = chtSht.ChartObjects("Chart 1")
    or by index
    Set chtObj = chtSht.ChartObjects(2)
    Set cht = chtObj.Chart

    now work with the "cht" etc

    Peter Thornton

    • Marked as answer by mr510 Tuesday, August 2, 2011 12:03 AM
    Saturday, July 30, 2011 8:43 AM
    Moderator

All replies

  • You can definitely work with two charts one a worksheet, I've done it many times. Try recording some code of you formatting both charts and you'll get what you need.

    Make sure you also have SP2 installed for Office 2007.


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Saturday, July 30, 2011 1:36 AM
  • Embedded charts chan be created on a Chart sheet just as in a Worksheet, IOW a chart on a chart. Manually a little bit of "trickery" is involved, but with code none at all as the method is the same. Try this in a new workbook -

    [bvbnet]
    Sub test()
    Dim i As Long
    Dim L As Double, T As Double, W As Double, H As Double
    Dim chtSht As Chart
    Dim chtObj As ChartObject, cht As Chart
    Dim sr As Series, ax As Axis
    Dim rng As Range
        Set rng = ActiveWorkbook.Worksheets(1).Range("A1:D4")
       rng.Formula = "=int(rand()*10)"
        Set chtSht = ActiveWorkbook.Charts.Add
        chtSht.ChartObjects.Delete
       W = chtSht.ChartArea.Width / 2
       H = chtSht.ChartArea.Height / 2
        For i = 1 To 4
           L = (i - 1) Mod 2
           L = W * L
           If i >= 3 Then
               T = H
           End If
            Set chtObj = chtSht.ChartObjects.Add(L, T, W, H)
           Set cht = chtObj.Chart
            Set sr = cht.SeriesCollection.NewSeries
           sr.Values = rng.Columns(i)
           sr.Name = "data " & i
            cht.HasTitle = True
           cht.ChartTitle.Text = chtObj.Name
            Set ax = cht.Axes(xlValue)
           ax.MaximumScale = 10
        Next
        MsgBox "PRESS  F9  TO RECALC"
    End Sub
    [/vbnet]

    To re-reference the embedded charts, fully reference like this
    Chart-Sheet, ChartObject, Chart
    eg

    Set chtSht = ActiveWorkbook.Charts("Chart 1")
    Set chtObj = chtSht.ChartObjects("Chart 1")
    or by index
    Set chtObj = chtSht.ChartObjects(2)
    Set cht = chtObj.Chart

    now work with the "cht" etc

    Peter Thornton

    • Marked as answer by mr510 Tuesday, August 2, 2011 12:03 AM
    Saturday, July 30, 2011 8:43 AM
    Moderator
  • Thanks for the quick response and VB code.  After some tweaks and struggle, I got something working. 

    I ran across a few quirks (IMHO), such as if you add a new chart with ActiveWorkbook.Charts.Add while a data range is in the clipboard (e.g., from a previous copy opeation), the chart won't be blank, but will have the clipboard contents as the data.  Also, some grumpiness from Excel with chtSht.ChartObjects.Delete, working sometimes, not working sometimes unless reordered. 

    But all in all a success. 

    FWIW, this is the functional but not ideal code that I ended up with (note that the "Temperature" sheet already has a chart on it:

       Sheets("Temperature").Activate
       ActiveSheet.Shapes.AddChart.Select
       ActiveChart.ChartType = xlXYScatter
       ActiveChart.SetSourceData Source:=Range("'Data'!$c$6:$C$1200,'Data'!$f$6:$f$1200")
       With ActiveChart.ChartArea
          .Height = 150
          .Width = 350
          .Top = 0
          .Left = 330
       End With

       ' do some formatting of the X and Y axes


       Set ax = ActiveChart.Axes(xlValue)
       With ax
          .HasMajorGridlines = False
          .HasMinorGridlines = False
          .HasTitle = True
          .AxisTitle.Font.Size = 12
          .MinimumScale = 300

          '...
       End With


       Set ax = ActiveChart.Axes(xlCategory)
       With ax
          .HasMajorGridlines = False
          .HasMinorGridlines = False
          .HasTitle = False
          .TickLabels.Font.Name = "Arial"
          .TickLabels.NumberFormat = "mm/dd"
          .TickLabels.Font.Size = 10
          ' ...

       End With

    Tuesday, August 2, 2011 12:13 AM