Method of adding a chart with a chart.add in a macro

  • Thread starter Thread starter ExcelNewbie
  • Start date Start date
E

ExcelNewbie

Hi all,

I am working with an excel based program that is in Excel 97 format.
Upon opening the program in 2000 it does prompt me with the warning
that there are macro compatibility issues.

I have developed a macro in Excel 2000 to use data to create a chart
but the script does not compile, it stops at the Chart.Add line ...a
friend ran it with Excel 97 and it worked fine. I have been trying to
find a solution to what seems to be a compatibility issue, or a
Chart.Add issue, or whatever it is...I have little knowledge of excel
and vb so hopefully this isn't too complicated.

Here's the code...

Selection.Copy
Sheets("Worksheet").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=7
Columns("L:T").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("O9").Select
ActiveCell.FormulaR1C1 = ""
Columns("K:L").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData
Source:=Sheets("Worksheet").Range("K1:L933"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Worksheet"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Percent MPE (%)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Tower
Height (feet)"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = True
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowNone,
LegendKey:=False
ActiveChart.Axes(xlCategory).MinorGridlines.Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 3").IncrementLeft -514.5
ActiveSheet.Shapes("Chart 3").IncrementTop -99.75
ActiveWindow.Visible = False
Windows("tc3.2g-97.xls").Activate
Sheets("Statistics").Select
Range("C3:G16").Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Sheets("Worksheet").Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveWindow.Visible = False
Windows("tc3.2g-97.xls").Activate
Range("F23").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=25
Selection.ShapeRange.IncrementLeft -81.75
Selection.ShapeRange.IncrementTop 233.25
Selection.ShapeRange.ScaleWidth 0.82, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.82, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 0.94, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.94, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.IncrementLeft 33.75
Selection.ShapeRange.IncrementTop -7.5
ActiveWindow.SmallScroll Down:=-18
Range("H17").Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 3").ScaleWidth 1.11, msoFalse,
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 3").ScaleHeight 2.52, msoFalse,
msoScaleFromTopLeft
ActiveWindow.Visible = False

I also resize the sheet and position it at the top left corner...tips
to help with that would be nice too....THANK YOU...
 
Good long description. Just one tiny problem. What is the error that
you get?

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top