Another VBA Chart problem Excel 97

[Karoo News]

Hi I have used macro recorder to create a chart on a sheet which is run
under sheet activate. The problem is the code referances 'Chart 1.... etc'
obviously each chart created on one of 37 sheets a new Chart number is
created so the code wont run.

Prob 1: Can "Chart 17" in the code be replaced with say activechart?
Prob 2: I need the graph to be placed in cells C2:AF10 is there a way I can
specify the exact size and position of the chart within the code?

Code Below

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Mon 2nd-w1").Range("A1")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='Mon 2nd-w1'!R43C3:R43C32"
ActiveChart.SeriesCollection(1).Values = "='Mon 2nd-w1'!R44C3:R44C32"
ActiveChart.SeriesCollection(2).Values = "='Mon 2nd-w1'!R16C3:R16C32"
ActiveChart.SeriesCollection(3).Values = "='Mon 2nd-w1'!R51C3:R51C32"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Mon 2nd-w1"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveSheet.Shapes("Chart 17").IncrementLeft -741#
ActiveSheet.Shapes("Chart 17").IncrementTop -546#
ActiveWindow.Visible = False
ActiveSheet.ChartObjects("Chart 17").Activate
ActiveChart.Axes(xlCategory).Select
ActiveWindow.Visible = False
Windows("Monthly Wage Tool - Blank 2006 v1_1.xls").Activate
Range("C1").Select
ActiveSheet.ChartObjects("Chart 17").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 17").ScaleHeight 0.7, msoFalse,
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 17").ScaleHeight 0.91, msoFalse,
msoScaleFromBottomRight


Many Thanks as always for your replies and most of all to Tom who has saved
me so much time Cheers

 

[Peter T]

Prob 1

ActiveChart.Parent.ShapeRange.ScaleWidth 0.7, msoFalse, msoScaleFromTopLeft


Prob 2

Set r = Range("C2:F10")
With ActiveChart.Parent
.Left = r.Left
.Top = r.Top
.Width = r.Width
.Height = r.Height
End With

did you really mean sized to C2:AF10 !

Regards,
Peter T

 

[Peter T]

Assuming you want to add a new "sized" chart - do the sizing first and no
need to select anything

Set r = Range("C2:F10")

With r
Set chtobj = ActiveSheet.ChartObjects.Add(.Left, .Top, .Width, .Height)
End With

With chtobj.Chart
'code

End With

 

[Karoo News]

Yes did mean size the chart to cover those cells!

Many thanks for your reply your a star