Multiple chart in one sheet

[oro77]

Hello,

I would like to put several charts in one sheet using VBA.

Using Charts.Add makes me a new Chart sheet each time, I would like to have only one.

One more question, is there a way to resize the charts with VBA ?

Thank you in advance

 

[Weaver]

Welcome to the board.

Here's some code I use to add a chart to the current worksheet and make changes to it. It probably won't exactly fit what you're trying to do as I can't know the specifics, but it might give you some ideas.

Charts.Add
            With ActiveChart
                .ChartType = xlXYScatterLinesNoMarkers
                .SetSourceData Source:=gRange, PlotBy:=xlColumns
                'next line forces graph to sheet known as gSheet (could be any sheet)
                .Location Where:=xlLocationAsObject, name:=gSheet.name
            End With
            With ActiveChart
                .HasTitle = True
                .HasLegend = False
                .ChartTitle.Characters.Text = dSheet.Cells(1, i + 1)
                .Axes(xlCategory, xlPrimary).HasTitle = False
                .Axes(xlValue, xlPrimary).HasTitle = False
                strName = Mid(.name, InStr(.name, "Chart"), 255)
            End With
            'this is how you resize the chart
            'here it's being set to fit a range of cells
            With ActiveSheet.Shapes(strName)
                .Left = 0
                .Top = Range("A" & dRow).Top
                .Width = w
                .Height = h
            End With
            With ActiveChart.Axes(xlCategory).TickLabels
                .NumberFormat = "dd/mm/yy"
                .AutoScaleFont = False
                .Font.Size = 8
            End With
            With ActiveChart.Axes(xlValue).TickLabels
                .NumberFormat = "#,##0"
                .AutoScaleFont = False
                .Font.Size = 8
            End With
            With ActiveChart.PlotArea
                .ClearFormats
                .Top = 0
                .Left = 0
                .Width = w
                .Height = h
            End With

HTH

 

[oro77]

Thank you very much, I will try with your code !

 

[oro77]

I am trying to understand your code but it seems there is only one Chart created, how should I do if I want to create other Charts on the same Sheet ?
Do I have to use :

.Location Where:=xlLocationAsObject, name:=gSheet.name

That's all ?

 

[oro77]

I managed to understand and use your code but I have one more question.

When I move the cart to the wanted sheet using :
.Location Where:=xlLocationAsObject, name:=gSheet.name

It makes a Shape with a default name, how can I change his name ?

I have a name problem when I add several charts, the shapes have the same name and I am not able to resize, move the correct one.

 

[Weaver]

Change

name:=gSheet.name

to the name of the sheet you want the chart to appear on

say if the tab is called "myCharts"

name:="myCharts"