Excel 2003 Macros in Excel 2007

M

mobyzone

Board Regular
Joined
Apr 17, 2008
Messages
73
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.
 
Sort by date Sort by votes
Ok apologies, when you do have chance please could you take a look? Apparantly you cant create custom graphs in excel 2007.
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi, the spreadsheet axis names and legend names do not show now like they would of done in 2003.

Would it be possible for me to send you the spreadsheet to have a look at?

Thanks
 
Upvote 0
Hi, yes the legends change fine but the horizontal axis just dont want to, if i select the data then the chart goes blank, its as if it doesnt even see the list of dates that I want it to show
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA problem with setting data source range
Replies
1
Views
852
Akbarov
A
J
  • Question
VBA Clear Chart Dynamically and refresh with new values
2
Replies
10
Views
1K
Jon Peltier
J
E
  • Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
2K
profray
P
Q
  • Question
Please help me, I am writing an excel vba chart1 code.
Replies
0
Views
534
Quan71
Q
L
  • Solved
VBA Help Modify Code - Prevent chart from resizing with columns / rows
Replies
3
Views
695
Anthony47
A

Forum statistics

Threads
1,214,911
Messages
6,122,199
Members
449,072
Latest member
DW Draft

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top