Error 1004 - unable to set the hastitle property of the axis class

G

Guest

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 Where:=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
 
J

Jim Cone

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" <[email protected]>
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 Where:=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
 
G

Guest

Thanks Jim, that did not work either !

Jim Cone said:
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" <[email protected]>
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 Where:=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
Click to expand...
 
G

Guest

Jim and Henri,

I also had the "No more new fonts may be applied in this workbook" error
message when producing a lot of graphs in one Excel file and found this link
from Microsoft.

I followed the directions and did the macro fix and the registry fix, which
did fix the problem that I had with the error message.

However....the problem now is that my new Excel files and any graphs that I
use from a new Excel file in another application (Powerpoint, Word) are VERY
large. For example, a Powerpoint file I had before the "fix" was ~6MB, and
now it's 28MB with new graphs substituted.

My question is this: How do I un-do the macro fix and registry fix that I
did earlier following the Microsoft instructions? That error probably
happened for 1 of every 20 Excel files that I have, and I'd much prefer to
deal with that than the problem that I'm having now.

Thanks for any help!
 
J

Jon Peltier

How were you inserting the charts, and how are you doing it now?

If you paste a chart into PowerPoint, you really are pasting a copy of the
entire workbook. Paste in two charts, that's two copies of the workbook.
Etc.

- Jon
 
G

Guest

Jon, That's very helpful to know. I hadn't realized it.
Do you know how I can un-do what I did to fix the problems in the first
place? I would prefer that things returned back to 'normal,' and I could
just not copy and paste so many charts!
Thank you.
 
J

Jon Peltier

I doubt the registry fix is causing your problem. I wonder if you used to
insert a picture of the chart or a link to the chart, and now you're
inserting the entire workbook.

- Jon
 
G

Guest

Jon,
I've always cut and pasted the charts the same way, so I don't think the
problem has anything to do with a change in that procedure. I changed the
registry and wrote a macro at the same time to try and get rid of the
problem, and noticed my problems w/ file size immediately after. Could my
problem have to do with the macro? If it's a possibility, how do I un-do a
macro?
Thanks again for your help.
Amber
 
J

Jon Peltier

The problem could relate to the macro, if it doesn't reproduce the exact
technique you used to use manually.

- Jon
 
G

Guest

So, do you know how I could un-do the macro? I don't use macros (ever), and
I just followed the instructions for this particular macro from the microsoft
website to fix the problem that I was having.
 
J

Jon Peltier

Unless the programmer put in something specific for undoing a macro (which
is unlikely), you would have to manually undo each of the changes the macro
made, or rebuild the chart, or go to the archived version of the file
(archives are also unlikely, in my experience).

- Jon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Runtime Errror 1004 No more new fonts may be applied in this workb 1
How to change the row number in macro code? 1
Graph - Runtime Error 1004 5
Creating Charts using VBA 0
Code stops for no apparent reason 4
add a chart in a Add-In 1
"Lines on 2 Axes" fails on Excel 2007 1
Chart error 2

Top