Issue creating charts in excel sheet

[i.sobha]

Hi,

I had written a vb code to create multiple charts in a single Excel
sheet.

The issue which I face is that in a single sheet the charts are
getting formed ( 9 charts in a single sheet) but all the charts appear
exactly one above the other. (t seems like only one chart is
generated) I am not able to position them such that they are placed
evenly.

Please find the code used to add charts to a sheet

The below code was placed in a loop to produce 9 charts in the sheet
'Sheet12"

With xlwCust.Charts.Add
.SetSourceData Source:=xlsCust.Range("A1:" & strCellRef),
PlotBy:=xlColumns
.ChartType = xlLine
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Percentage"
.Legend.Position = xlLegendPositionBottom
.ChartType = xlLine
.SizeWithWindow = True
.Location Where:=xlLocationAsObject, Name:="Sheet12"
End With

Please could somebody help.

Thanks and Regards,
Sobha

 

[Bernie Deitrick]

Sobha,

After you run your first macro, run the macro below. Change the constants to get your desired
spacing. You can also change the code to make two or more columns instead of just one.

HTH,
Bernie
MS Excel MVP

Sub SpaceOutCharts2()
Dim i As Integer
Dim NumCharts As Integer
Dim myTop As Integer
Dim myLeft As Integer
Dim TopInc As Integer
Dim myCell As Range


Set myCell = Range("E1")


'Find the number of charts
NumCharts = ActiveSheet.Shapes.Count


'Specify the cell for the first chart - E1 in this example
myTop = myCell.Top
myLeft = myCell.Left


'Specify the spacing of the charts
TopInc = 255


For i = 1 To NumCharts
With ActiveSheet.Shapes(i)
.Left = myLeft
.Top = myTop + TopInc * (i - 1)
End With
Next i


End Sub

 

[i.sobha]

Hi Bernie,

Still I face issues.

NumCharts = ActiveSheet.Shapes.Count always gives me a zero value.

I tried getting the name of the Active sheet (Activesheet.Name) but it
does not give me any value.

Thanks,
Sobha