Excel 2003 Macros in Excel 2007

[mobyzone]

Hi there,

I have a spreadsheet that collates a list of data from week order and puts them in a graph. I have a macro created that basically does the graph, it works fine in 2003 but not in office 2007. I get an error "Run Time Error '1004' Application-defined or object-defined error, then when I click the debug button it highlights "ActiveSheet.ChartObjects.Delete". I'm not sure if when its trying to create a new graph its attempting to delete the old one and isnt deleting it? The full code for the macro is below:

Sub updateStuff()
'Macro by Paul Craddy - IT

Dim searchResult As Range 'Result of the find operation - to check if it has worked or not

'Shut off screen updating to avoid confusing user
Application.ScreenUpdating = False
'Check the chart already exists, and delete it if so
If SheetExists("OTIF Chart") Then
Application.DisplayAlerts = False
Sheets("OTIF Chart").Delete
Application.DisplayAlerts = True
End If
'Copy the headings to the data sheet
Sheets("OTIF's").Select
Range("B4:N4").Select
Selection.Copy
Sheets("Graph").Select
Range("B1").Select
ActiveSheet.Paste
'Delete any existing data
Range("a2:l8192").Clear
Sheets("Macros").Select
Range("D25").Value = Application.InputBox(prompt:="Number of weeks to plot", Type:=1, Default:="52", Title:="WEEKS TO PLOT")
GoTo LastDate:
GraphDate:
Sheets("Macros").Select
Range("D24").Select
ActiveSheet.Paste
Range("D24").Value = Application.InputBox(prompt:="Enter last week to plot", Type:=2, Default:=Range("D24").Value, Title:="LAST TO PLOT")
intWeeksToPlot = Range("D25").Value
intWeeksToPlotNeg = intWeeksToPlot - intWeeksToPlot * 2
strDateToFind = Range("D24").Value
strXAxisTitle = Range("F26").Value
strYAxisTitle = Range("F27").Value
strChartTitle = Range("F24").Value
strChartSubTitle = Range("F25").Value
Sheets("OTIF's").Select
Set searchResult = Cells.Find(what:=strDateToFind)
If searchResult Is Nothing Then
MsgBox "Date not found!", vbExclamation, "Error!"
GoTo EndCode:
Else
Cells.Find(what:=strDateToFind).Select
End If
Selection.Offset(intWeeksToPlotNeg + 1, 0).Select
Selection.Resize(intWeeksToPlot + 1, 14).Select
Selection.Copy
Sheets("Graph").Select
Range("A2").Select
ActiveCell.PasteSpecial xlPasteValues
Columns("B").Delete
Columns("L").Delete
'Format date values as dates to enure proper display
Columns("A:A").Select
Range("A28").Activate
Selection.NumberFormat = "dd mmm yyyy"
'Paste in the dummy target values
Range("M1").Select
ActiveCell.FormulaR1C1 = "Target 2003"
Range("M2").Select
ActiveCell.FormulaR1C1 = "99.75"
Range("M2").Select
Selection.Copy
Range("M2:M54").Select
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveSheet.ChartObjects.Delete < ERRORS AT THIS POINT
Range("B2").Select
Selection.Resize(intWeeksToPlot + 1, 11).Select
ActiveSheet.ChartObjects.Add(0, 0, 700, 500).Select
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes"
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("B1:B52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("C1:C52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("D1:D52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("E1:E52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("F1:F52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("G1:G52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("H1:H52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("I1:I52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("J1:J52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("K1:K52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("L1:L52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlLine
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).AxisGroup = xlSecondary
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).MarkerStyle = xlNone
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Smooth = False
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("M1:M52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlLine
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).AxisGroup = xlSecondary
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).MarkerStyle = xlNone
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Smooth = False
ActiveChart.HasLegend = True
ActiveChart.Legend.Position = xlLegendPositionBottom
With ActiveChart.ChartGroups(1)
.Overlap = 100
.GapWidth = 50
.HasSeriesLines = False
End With
With ActiveChart.Axes(xlValue, xlPrimary)
.MinimumScale = 0
.MaximumScale = 10
End With
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Caption = strChartTitle & Chr(13) & strChartSubTitle
ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Caption = strXAxisTitle
ActiveChart.Axes(xlValue, xlSecondary).HasTitle = True
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Caption = strYAxisTitle
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = 97
.MaximumScale = 100
End With
ActiveChart.SeriesCollection(1).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(2).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(3).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(4).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(5).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(6).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(7).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(8).XValues = "=Graph!C1"
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 4
.TickMarkSpacing = 1
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
ActiveChart.Location xlLocationAsNewSheet, "OTIF Chart"
ActiveChart.PlotArea.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With

Selection.Interior.ColorIndex = xlNone

ActiveChart.SeriesCollection(11).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With

'ActiveChart.SeriesCollection(12).Select
'With Selection.Border
' .ColorIndex = 1
' .Weight = xlThin
' .LineStyle = xlDashDot
'End With

ActiveChart.SeriesCollection(12).Select
With Selection.Border
.ColorIndex = 10
.Weight = xlThin
.LineStyle = xlContinuous
End With

GoTo EndCode
LastDate:
Sheets("OTIF's").Select
'Goto first date search value
Range("R7").Select
Checker4:
'Search for latest date
If CDate(ActiveCell.Value) >= Date Or ActiveCell.Value = "" Then
GoTo Found4
End If
ActiveCell.Offset(1, 0).Select
GoTo Checker4
Found4:
'Select the row we have found the date on
ActiveCell.Offset(0, -17).Select
Selection.Copy
GoTo GraphDate
EndCode:
Application.ScreenUpdating = True
End Sub

Thank you so much for your help, this forum is fantastic.

 

[Andrew Poulsom]

That line of code worked for me in Excel 2007. It fails with the error you had if there are no ChartObjects on the sheet. It doesn't fail in Excel 2003. You will need to trap the error.

 

[mobyzone]

Hi, how can I do that?

Thanks!

 

[Andrew Poulsom]

Like this?

On Error Resume Next
ActiveSheet.ChartObjects.Delete 
On Error Goto 0

 

[mobyzone]

Hi,

Thanks i've done that now i'm getting an error "Run-time Error 91, Object variable or With block variable not set"

Erroring at:

ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes"

 

[Andrew Poulsom]

Sorry, I don't have access to Excel 2007 at the moment. Try recording a macro to get the syntax.

 

[mobyzone]

Hi, i've done that error trapping code again and it continues on but the legends are wrong now. Comes up with series 2 3 etc instead of the names

 

[Andrew Poulsom]

It's no good skipping over that code just because it doesn't work. You need to find the equivalent code that does work in Excel 2007.

I only suggested trapping the error for your first problem, because the code only fails if there are no ChartObjects on the sheet. That failure is inconsequentional (no charts to delete) and can therefore be skipped.

 

[mobyzone]

so what code for this would work in excel 2007?

ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes" <!-- / message -->

 

[Andrew Poulsom]

I don't know. As I said before I don't have access to Excel 2007 at the moment.