Error 1004 executing macros

[Benn]

hi,

I have about 118 charts in excel. I have also 118 macros
each one associated with a chart. I also have a macro
called "RunAllMacros" which have macro calls like this
eg.
Run "ThisWorkbook.ChartMacro0"
..
..
Run "ThisWorkbook.ChartMacro49"
..
..
Run "ThisWorkbook.ChartMacro118"

When I try to run the macro "RunAllMacros"
I get an error "1004 application defined or object define
error". I have read the article about this error, but
I am having trouble fixing it. This error only occurrs
after is has executed the 48th macro. Here is the article
link : http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/q
178/5/10.asp&NoWebContent=1

I have provided the code to one of the 118 macros below,
they are all the same except for the actual chart name.

Can anyone offer some advise on how to fix this

Thanks in advance.
-------------------------------------------------

Sub ChartMacro49()
Dim Counter As Integer
Dim ModValue As Integer
Dim UseColor As Integer
'***SET
LABELS****************************************************
***************
ActiveSheet.ChartObjects("Chart 55").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.ChartArea.Select
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabel,
LegendKey:=False, HasLeaderLines:=True
ActiveSheet.ChartObjects("Chart 55").Activate
ActiveChart.SeriesCollection(1).DataLabels.Select
With Selection
..HorizontalAlignment = xlCenter
..VerticalAlignment = xlCenter
..Position = xlLabelPositionInsideEnd
..Orientation = xlHorizontal
End With
'***SET
COLORS****************************************************
***************
ActiveSheet.ChartObjects("Chart 55").Activate
ActiveChart.SeriesCollection(1).Select
For Counter = 1 To ActiveChart.SeriesCollection
(1).Points.Count
ActiveChart.SeriesCollection(1).Points(Counter).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
ModValue = Counter Mod 2
If (ModValue > 0) Then
UseColor = 35
Else
UseColor = 22
End If
With Selection.Interior
..ColorIndex = UseColor
..Pattern = xlSolid
End With
Next
'---------------------------------------------------------
-----------------------
'***SET BORDERS AND CHART
SIZE***************************************************
ActiveSheet.ChartObjects("Chart 55").Activate
ActiveChart.PlotArea.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Interior.ColorIndex = xlNone
Selection.Left = 14
Selection.Top = 1
Selection.Width = 95
Selection.Height = 95
ActiveChart.ChartArea.Select
With Selection.Border
.Weight = 1
.LineStyle = 0
End With
Selection.Interior.ColorIndex = xlNone
Sheets("Coverage Report").DrawingObjects("Chart
55").RoundedCorners = False
Sheets("Coverage Report").DrawingObjects("Chart
55").Shadow = False
ActiveWindow.Visible = False
'***SET FONT
SIZE******************************************************
**********
ActiveSheet.ChartObjects("Chart 55").Activate
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.AutoScaleFont = True

With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveSheet.ChartObjects("Chart 55").Activate
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
Selection.Height = 73
Selection.Width = 73
Selection.Left = 25
Selection.Top = 23
End Sub

 

[Tom Ogilvy]

You need to change
Run "ThisWorkbook.ChartMacro0"

to
Run ThisWorkbook.Name & "!ChartMacro0"

if ThisWorkbook.Name has a space in it then you need

Run "'" & ThisWorkbook.Name & "'!ChartMacro0"

or you can just use the name of the workbook if it won't change

Run "'My Workbook.xls'!ChartMacro"

 

[NickHK]

Benn,
Not sure the cause of the error, but you can greatly simplify your code (by
a factor of 1/118) by using a function (or sub) to format all your charts,
passing the number (or whole name if required) to the routine. This assume
that all charts are formatted the same.

Use the "Set ThisChart = ActiveSheet.ChartObjects("Chart " & argIndex)" so
you always deal with the same variable.
Use of "With" will also simply and clarify the code.
Also, there's no need for all the .Select/.Activate. I suppose this is the
code that the macro recorder generated. Whilst a great tool, it gives you
unneccesary line sometimes.
Call the function below from the click event of some control for example
Private Sub CommandButton1_Click()
Dim RetVal As Long

RetVal = FormatChart(1)

End Sub

Addition of error handling in case the chart does not exist would be a good
idea

Private Function FormatChart(argIndex As Long) As Long
Dim Counter As Integer
Dim ModValue As Integer
Dim UseColor As Integer
Dim ThisChart As Chart

Set ThisChart = ThisWorkbook.Worksheets(1).ChartObjects(argIndex).Chart

'***SET LABELS****************************************************
ThisChart.ApplyDataLabels Type:=xlDataLabelsShowLabel, LegendKey:=False,
HasLeaderLines:=True

With ThisChart
With .SeriesCollection(1)
With .DataLabels
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Position = xlLabelPositionInsideEnd
.Orientation = xlHorizontal
End With
....
.... etc

Hope this helps

NickHK

 

[Benn]

Hi NickHK
Thanks for the advice.I wanted to know what the variable
"ThisChart" is. I have declared it as a chart, and when
I run the macro an error occurrs "Type mismatch".
If I do not declare the variable at all I received the
error "object does not support property or method"
on the second line of these statements:

Set ThisChart = ActiveSheet.ChartObjects("Chart 55")
ThisChart.SeriesCollection(1).Select


it was originally :

ActiveSheet.ChartObjects("Chart 54").Activate
ActiveChart.SeriesCollection(1).Select


Thanks

 

[Tom Ogilvy]

Dim ThisChart as Chart
Set ThisChart = ActiveSheet.ChartObjects("Chart 55").Chart
ThisChart.SeriesCollection(1).Select