Solved: Creating multiple charts in single worksheet vith VBA

[tmatematikas]

Hello, I was writing some interesting code for adding multiple charts to single worksheet. The code above works fine if I place all charts into separate sheets (.Location Where:=xlLocationAsNewSheet), but I get errors with (.Location Where:=xlLocationAsObject, Name:=sheetTitle):

Public Function Chart(penketas As Integer, turis As Integer)

sheetTitle = "charts"
'Dim chtChart As Chart 'tried also
'Set chtChart = Charts.Add
Charts.Add

With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("1-" & turis & "-freq").Range("B3:B12").Offset(0, 2 * (penketas - 1)), PlotBy:=xlColumns
.SeriesCollection(1).XValues = "='1-" & turis & "-freq'!" & "R3C" & (2 * penketas - 1) & ":R12C" & (2 * penketas - 1)
'.Location Where:=xlLocationAsNewSheet
.Location Where:=xlLocationAsObject, Name:=sheetTitle
.ChartTitle.Characters.Text = "Sumos pasiskirstymo histogram after " & (5 * penketas) & " year"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Start point"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Freq"
.HasLegend = False
.HasDataTable = False
End With

End Function

Where is the mistake? Maybe some kind of type mismatch? When I comment everything starting .ChartTitle and ending .HasDataTable the code works :dunno.

[Demosthine]

Good Afternoon.

Would you please post your workbook for us. That'll make it easier than recreating it and ensure more accuracy with our ansers.

Scott

[tmatematikas]

Here it is: http:// tmatematikas.mikrovisata.net/dokumentai/MultipleCharts.xls

[Demosthine]

Hey there.

Thanks for the workbook. That definitely helped.

When I first ran the code, it gave me an error on Line 55 which is your .SetDataSource. It is giving an error there because you are trying to use Worksheet '1-1000-da?niai' and this particular sheet does not exist in the Workbook you provided. I changed the text 'da?niai' to 'da?niai' because you do have a sheet with that name. Note you'll also need to change this in your .SeriesCollections, too.

Once I made the above corrections, the .ChartTitle caused a different error. This time, it is an Automation Error. It would appear that when you set the .Location, the chart is no longer the ActiveChart. If you move this line down so that it is directly above End With, the procedure seems to work beautifully. Nice job.

The last thing I'll mention is that you have to set .HasTitle = True before you can assign .ChartTitle.

Good Luck.
Scott

[tmatematikas]

Thank's a lot. Results look pretty good :yes. About that "ž", this might be lost, because I "cut" this sheet from entire book and my PC has Lithuanian as default language for all applications while your's might be different. Am I right?

[Demosthine]

Yes, you could absolutely be right that that may have been lost in translation. Yes, mine is definitely a different language: English. I wouldn't even be able to recognize that a language was Lithuanian, much less understand it...

Take care.
Scott