How to place multiple Excel charts evenly in one chart sheet?

0
votes

How do I place four chartobjects in a single Excel chartsheet with four ranges of data set?

I create all four charts in a chart sheet. I am struggling to resize the first chartobject since it looks like the chart size is fixed.
If I create a chart in a chart sheet, is it going to be the fixed size, fitting to the entire screen?

Also, I tried to select each chart using chart index, but the first chartobject doesn't have the index, resulting in getting only three index out of four chartobjects.
The code is not working since the second import chart will be index 1 which won't match to ForLoop index.

    For i = 1 To cnt_dataset - 1

        Range((Cells(data_array(i, 1), 21)), (Cells(data_array(i, 2), 22))).Select
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlLine
        ActiveChart.ApplyLayout (10)
        ActiveChart.ChartGroups(1).HiLoLines.Select
        Selection.Delete

        If i = 1 Then
            ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart"

        Else
            ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"
            ActiveSheet.ChartObjects(i).Activate

        End If

        With ActiveChart
            .ChartTitle.Text = "Chart A"
            .Axes(xlValue, xlPrimary).AxisTitle.Text = "y"
            .Axes(xlCategory, xlPrimary).AxisTitle.Text = "x"
            .Axes(xlCategory).Select
        End With

        Selection.TickLabels.NumberFormat = "#,##0"
        ActiveWindow.WindowState = xlMaximized          

    Next i
2
excelvba
I just did a google search for "vba activechart size location" and this is the first result... peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html – alowflyingpig
If I move the chartobject as a new sheet, there won't be the chart index which seems to be not mentioned in the site. Feel free to correct me plz. Thx. – Live2Win
If you read the article and scroll thru it it shows how to resize and reposition a chart.. Also how to CoverRangeWithAChart. Failing that you could "always record a macro to find out how to do something". – alowflyingpig
If the command 'ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart"' is executed, the chart moves to new chart sheet and then I can't even manually resize the chart any longer. This is my point if I readdress it.. – Live2Win

2 Answers

0
votes

When you create a chart sheet, the chart you see is in the chart sheet, not in a container called a ChartObject. So you need a blank chart sheet, and you need to embed all of your charts as ChartObjects in this chart sheet.

Replace this bit:

    If i = 1 Then
        ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart"

    Else
        ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"
        ActiveSheet.ChartObjects(i).Activate

    End If

with this

    ' create reference to new chart
    Dim cht As Chart
    Set cht = ActiveChart

    If i = 1 Then
        ' add chart sheet and make it blank            
        ActiveWorkbook.Charts.Add
        ActiveChart.Name = "Chart"
        ActiveChart.ChartArea.Clear

    End If
    cht.Location Where:=xlLocationAsObject, Name:="Chart"
    ActiveSheet.ChartObjects(i).Activate
0
votes

Using this simple data...

simple data range

...here is simple code to create a blank chart sheet, then make simple charts directly on this chart sheet. 

Sub PutChartsIntoChartSheet()
  Dim ws As Worksheet
  Set ws = ActiveSheet
  Dim rng As Range
  Set rng = ActiveSheet.UsedRange

  Dim cht As Chart
  Set cht = ActiveWorkbook.Charts.Add
  cht.ChartArea.Clear

  Dim rX As Range
  Set rX = rng.Columns(1)

  Dim iCht As Long
  For iCht = 1 To rng.Columns.Count - 1
    Dim rY As Range
    Set rY = rX.Offset(, iCht)

    Dim cht2 As Chart
    Set cht2 = cht.Shapes.AddChart(xlLine).Chart
    cht2.SetSourceData Union(rX, rY)
  Next
End Sub

I've made no attempts to embellish the charts or arrange them nicely on the chart sheet.

This should run without issues.