At some point it's your active chart, so just use ActiveChart. Actually
Activehart.Parent to adjust the ChartObject. I hate using the Shape
object when editing a chart, so I use the Chart Object.

With ActiveChart.Parent
.Left = left coordinate in points
.Top = top coordinate in points
.Width = width in points
.Height = height in points
End With

If you want to line the chart up with a range of cells, that's easy too:

Set rngChart = ActiveSheet.Range("D4:K20")
With ActiveChart.Parent
.Left = rngChart.Left
.Top = rngChart.Top
.Width = rngChart.Width
.Height = rngChart.Height
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


z.entropic wrote:

I cannot deny that you were absolutely right on three counts: the quotes (whose import I completely missed at the time), my inability then to grasp your solution, and the need to re-read the thread knowing what I know now. Actually, this is how I found the hyphen-minus problem.

Here's a bonus question ;-): by default, the created chart is small. I tried to incorporate a resizing statement by recording a little macro separately and incorporating it into the main one, but my trick didn't work because of, again, the specific and variable chart naming problem--"Chart 3" in

Sub ResizeChart()
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 3").IncrementLeft -169.5
ActiveSheet.Shapes("Chart 3").IncrementTop -108.75
ActiveSheet.Shapes("Chart 3").ScaleWidth 1.92, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 3").ScaleHeight 1.91, msoFalse, msoScaleFromTopLeft
End Sub

I've tried all kinds of substitutions using sSheet for "Chart 3", but no cigar... the entire macro is listed below.

Thanks for your suggestions and help.

z.entropic

Sub VQChart()
'' declare variables for active sheet name and selected range
Dim sSheet As String
Dim rRange As Range
Dim sAddr As String
sSheet = ActiveSheet.Name

'' add chart and put it where you want it
Charts.Add
ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheet

'' Delete first series
Do While ActiveChart.SeriesCollection.Count 0
ActiveChart.SeriesCollection(1).Delete
Loop

'' Add first series (sometimes charts.add does, sometimes not)
If ActiveChart.SeriesCollection.Count = 0 Then
ActiveChart.SeriesCollection.NewSeries
End If

'' Define the data and type
ActiveChart.SeriesCollection(1).Values = _
"='" & sSheet & "'!R2C8:R1000C8"
ActiveChart.SeriesCollection(1).XValues = _
"='" & sSheet & "'!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "='" & sSheet & "'!R1C9"
ActiveChart.ChartType = xlXYScatterLinesNoMarkers

'' Add second series and define data
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = _
"='" & sSheet & "'!R2C8:R1000C8"
ActiveChart.SeriesCollection(2).XValues = _
"='" & sSheet & "'!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Name = "='" & sSheet & "'!R1C10"

ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "capacity, Ah"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "voltage, V"
End With
ActiveChart.Legend.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.Width = 135
Selection.Height = 36
Selection.Left = 186
Selection.Top = 128
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 1
.Weight = xlHairline
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 2
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub



"Tushar Mehta" wrote:


Ummm...if you go back to my June 10 post, you'll find that the proposed
code includes single quotes around the worksheet name. As you noted in
a subsequent post, the tips might have been too cryptic to be useful at
that point. Now, given the (painful? g) experience of the past week,
it might be worth your while to revisit those earlier posts ;-)

--
Regards,

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