Issue creating charts in excel sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

i.sobha@gmail.com - 26 Jun 2007 12:47 GMT

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

Reply to this Message

Bernie Deitrick - 26 Jun 2007 15:50 GMT

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

> Hi,
>
[quoted text clipped - 31 lines]
> Thanks and Regards,
> Sobha

Reply to this Message

i.sobha@gmail.com - 27 Jun 2007 09:11 GMT

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

Reply to this Message

Rate this thread: