I have to create a large number of graphs from an excel dataset.
I am successful for about 2/3 of the dataset. I get a Runtime Error 1004:
Unable to set the HasTitle property of the axis class.

The total amount of datasets is 50
The maximum amount of graphs is about 27


Option Explicit
Dim p As Integer
Dim q As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim chartcounter As Integer
Dim chartcolumn As Integer
Dim countvalues As Integer
Dim countrecords As Integer
Dim offset As Integer
Dim var1 As String
Dim ChtObj As ChartObject
Dim mc As Range

Private Sub CommandButton1_Click()
z = 8
y = 5
q = 1
countvalues = 0
countrecords = 0
chartcounter = 1
chartcolumn = 0
offset = 1

Do While Sheet1.Cells(z, 1) < ""
countrecords = countrecords + 1
If Sheet1.Cells(z, 1) < Sheet1.Cells(z + 1, 1) Then

Sheet2.Activate
Set mc = ActiveSheet.Cells(chartcounter, 1)

chartcounter = chartcounter + 15

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Sheet1.Range(Cells(z - countrecords + 1, y),
Cells(z - countrecords + countvalues, y + 1)), PlotBy _
:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Sheet1.Cells(z, 1) & " " &
Sheet1.Cells(1, y + 1)
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "concentration
mg/l"
End With
With ActiveChart.Parent
.Top = Range(mc.Address).Top
.Left = Range(mc.Address).Left
End With


countrecords = 0
countvalues = 0
End If
If Sheet1.Cells(z, y) < "" Then
countvalues = countvalues + 1
End If
z = z + 1
Loop

End Sub

Maybe...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Option Explicit
Dim p As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim chartcounter As Integer
Dim chartcolumn As Integer
Dim countvalues As Integer
Dim countrecords As Integer
Dim offset As Integer
Dim var1 As String
Dim ChtObj As ChartObject
Dim mc As Range

Private Sub CommandButton1_Click()
countvalues = 0
countrecords = 0
chartcounter = 1
chartcolumn = 0
offset = 1
z = 8
y = 5

Do While Sheet1.Cells(z, 1) < ""
countrecords = countrecords + 1
If Sheet1.Cells(z, 1) < Sheet1.Cells(z + 1, 1) Then

Sheet2.Activate
Set mc = ActiveSheet.Cells(chartcounter, 1)

chartcounter = chartcounter + 15

Set ChtObj = Sheet2.ChartObjects.Add(Range(mc.Address).Left, Range(mc.Address).Top, 300, 200)
ChtObj.Chart.ChartType = xlXYScatter
With Sheet1
ChtObj.Chart.SetSourceData .Range(.Cells(z - countrecords + 1, y), _
.Cells(z - countrecords + countvalues, y + 1)), PlotBy:=xlColumns
End With
With ChtObj.Chart
.HasTitle = True
.ChartTitle.Characters.Text = Sheet1.Cells(z, 1) & " " & Sheet1.Cells(1, y + 1)
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "concentration mg/l"
End With

countrecords = 0
countvalues = 0
End If
If Sheet1.Cells(z, y) < "" Then
countvalues = countvalues + 1
End If
z = z + 1
Loop
Set ChtObj = Nothing
End Sub
'-------------------------------




"Henri"
wrote in message
I have to create a large number of graphs from an excel dataset.
I am successful for about 2/3 of the dataset. I get a Runtime Error 1004:
Unable to set the HasTitle property of the axis class.

The total amount of datasets is 50
The maximum amount of graphs is about 27


Option Explicit
Dim p As Integer
Dim q As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim chartcounter As Integer
Dim chartcolumn As Integer
Dim countvalues As Integer
Dim countrecords As Integer
Dim offset As Integer
Dim var1 As String
Dim ChtObj As ChartObject
Dim mc As Range

Private Sub CommandButton1_Click()
z = 8
y = 5
q = 1
countvalues = 0
countrecords = 0
chartcounter = 1
chartcolumn = 0
offset = 1

Do While Sheet1.Cells(z, 1) < ""
countrecords = countrecords + 1
If Sheet1.Cells(z, 1) < Sheet1.Cells(z + 1, 1) Then

Sheet2.Activate
Set mc = ActiveSheet.Cells(chartcounter, 1)

chartcounter = chartcounter + 15

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Sheet1.Range(Cells(z - countrecords + 1, y),
Cells(z - countrecords + countvalues, y + 1)), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Sheet1.Cells(z, 1) & " " &
Sheet1.Cells(1, y + 1)
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "concentration mg/l"
End With
With ActiveChart.Parent
.Top = Range(mc.Address).Top
.Left = Range(mc.Address).Left
End With

countrecords = 0
countvalues = 0
End If
If Sheet1.Cells(z, y) < "" Then
countvalues = countvalues + 1
End If
z = z + 1
Loop

End Sub

Thanks Jim, that did not work either !

"Jim Cone" wrote:

Maybe...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Option Explicit
Dim p As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim chartcounter As Integer
Dim chartcolumn As Integer
Dim countvalues As Integer
Dim countrecords As Integer
Dim offset As Integer
Dim var1 As String
Dim ChtObj As ChartObject
Dim mc As Range

Private Sub CommandButton1_Click()
countvalues = 0
countrecords = 0
chartcounter = 1
chartcolumn = 0
offset = 1
z = 8
y = 5

Do While Sheet1.Cells(z, 1) < ""
countrecords = countrecords + 1
If Sheet1.Cells(z, 1) < Sheet1.Cells(z + 1, 1) Then

Sheet2.Activate
Set mc = ActiveSheet.Cells(chartcounter, 1)

chartcounter = chartcounter + 15

Set ChtObj = Sheet2.ChartObjects.Add(Range(mc.Address).Left, Range(mc.Address).Top, 300, 200)
ChtObj.Chart.ChartType = xlXYScatter
With Sheet1
ChtObj.Chart.SetSourceData .Range(.Cells(z - countrecords + 1, y), _
.Cells(z - countrecords + countvalues, y + 1)), PlotBy:=xlColumns
End With
With ChtObj.Chart
.HasTitle = True
.ChartTitle.Characters.Text = Sheet1.Cells(z, 1) & " " & Sheet1.Cells(1, y + 1)
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "concentration mg/l"
End With

countrecords = 0
countvalues = 0
End If
If Sheet1.Cells(z, y) < "" Then
countvalues = countvalues + 1
End If
z = z + 1
Loop
Set ChtObj = Nothing
End Sub
'-------------------------------




"Henri"
wrote in message
I have to create a large number of graphs from an excel dataset.
I am successful for about 2/3 of the dataset. I get a Runtime Error 1004:
Unable to set the HasTitle property of the axis class.

The total amount of datasets is 50
The maximum amount of graphs is about 27


Option Explicit
Dim p As Integer
Dim q As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim chartcounter As Integer
Dim chartcolumn As Integer
Dim countvalues As Integer
Dim countrecords As Integer
Dim offset As Integer
Dim var1 As String
Dim ChtObj As ChartObject
Dim mc As Range

Private Sub CommandButton1_Click()
z = 8
y = 5
q = 1
countvalues = 0
countrecords = 0
chartcounter = 1
chartcolumn = 0
offset = 1

Do While Sheet1.Cells(z, 1) < ""
countrecords = countrecords + 1
If Sheet1.Cells(z, 1) < Sheet1.Cells(z + 1, 1) Then

Sheet2.Activate
Set mc = ActiveSheet.Cells(chartcounter, 1)

chartcounter = chartcounter + 15

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Sheet1.Range(Cells(z - countrecords + 1, y),
Cells(z - countrecords + countvalues, y + 1)), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Sheet1.Cells(z, 1) & " " &
Sheet1.Cells(1, y + 1)
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "concentration mg/l"
End With
With ActiveChart.Parent
.Top = Range(mc.Address).Top
.Left = Range(mc.Address).Left
End With

countrecords = 0
countvalues = 0
End If
If Sheet1.Cells(z, y) < "" Then
countvalues = countvalues + 1
End If
z = z + 1
Loop

End Sub

It worked for me.
Jim Cone
San Francisco, USA


"Henri"
wrote in message
Thanks Jim, that did not work either !

"Jim Cone" wrote:
Maybe...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Option Explicit
Dim p As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim chartcounter As Integer
Dim chartcolumn As Integer
Dim countvalues As Integer
Dim countrecords As Integer
Dim offset As Integer
Dim var1 As String
Dim ChtObj As ChartObject
Dim mc As Range

Private Sub CommandButton1_Click()
countvalues = 0
countrecords = 0
chartcounter = 1
chartcolumn = 0
offset = 1
z = 8
y = 5

Do While Sheet1.Cells(z, 1) < ""
countrecords = countrecords + 1
If Sheet1.Cells(z, 1) < Sheet1.Cells(z + 1, 1) Then

Sheet2.Activate
Set mc = ActiveSheet.Cells(chartcounter, 1)

chartcounter = chartcounter + 15

Set ChtObj = Sheet2.ChartObjects.Add(Range(mc.Address).Left, Range(mc.Address).Top, 300, 200)
ChtObj.Chart.ChartType = xlXYScatter
With Sheet1
ChtObj.Chart.SetSourceData .Range(.Cells(z - countrecords + 1, y), _
.Cells(z - countrecords + countvalues, y + 1)), PlotBy:=xlColumns
End With
With ChtObj.Chart
.HasTitle = True
.ChartTitle.Characters.Text = Sheet1.Cells(z, 1) & " " & Sheet1.Cells(1, y + 1)
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "concentration mg/l"
End With

countrecords = 0
countvalues = 0
End If
If Sheet1.Cells(z, y) < "" Then
countvalues = countvalues + 1
End If
z = z + 1
Loop
Set ChtObj = Nothing
End Sub
'-------------------------------

http://support.microsoft.com/kb/215573/en-us
"You receive an error message when you add a chart to a workbook in Excel...
No more new fonts may be applied in this workbook."

--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

The registry hack was the solution. Thank you so much !

"Jim Cone" wrote:

http://support.microsoft.com/kb/215573/en-us
"You receive an error message when you add a chart to a workbook in Excel...
No more new fonts may be applied in this workbook."

--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware