Hi All

I'm trying to align a rectangle (called mySpacer) with the Left edge of
Point(x) in a SeriesCollection. The following line of code returns error 438
so I'm presuming
the 'Points' object doesn't have a Left value.

ActiveChart.Shapes("mySpacer").Left =
ActiveChart.SeriesCollection(1).Point(x).Left

Can you steer me in the right direction?
(I've looked at Jon Peltier's site but can't see anything obvoius.)

Hi,

You will need to calculate the position using other information in the chart
that does return a Left/Top/Width/Height property.

Can you provide a little more information such as chart type, how should the
shape be positioned vertically.
And which xl version are you using?

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Trevor Williams" wrote in
message ...
Hi All

I'm trying to align a rectangle (called mySpacer) with the Left edge of
Point(x) in a SeriesCollection. The following line of code returns error
438
so I'm presuming
the 'Points' object doesn't have a Left value.

ActiveChart.Shapes("mySpacer").Left =
ActiveChart.SeriesCollection(1).Point(x).Left

Can you steer me in the right direction?
(I've looked at Jon Peltier's site but can't see anything obvoius.)

Hi Andy - thanks for the quick response.

I'm using XL2002. The chart is a ColumnChart.
I need to add a predefined image above each point in the series collection.
The images are named "pic1, pic2..." etc.

I resize 'mySpacer' by dividing the the Category Axis width by the count of
the points in the chart. 'mySpacer' is aligned to the Value Axis Top -20.

I want to align mySpacer to the left of each point and then align the image
centrally to mySpacer.

Here's my code to align the images...

Sub mcr_Add_Comms_Potential_Dots_To_Graphs()
Dim cell As Range

x = 1

For Each cell In Range("D38:D67")
If cell = "" Then Exit For
ActiveSheet.Shapes("pic" & x).Cut
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart
.Paste
With .Shapes.Range(Array("pic" & x, "mySpacer"))
.Align msoAlignBottoms, False
.Align msoAlignRights, False
.Align msoAlignCenters, False
.Align msoAlignMiddles, False
End With

ActiveChart.Shapes("mySpacer").Left =
ActiveChart.SeriesCollection(1).Point(x + 1).Left

End With
x = x + 1
Next

End Sub



"Andy Pope" wrote:

Hi,

You will need to calculate the position using other information in the chart
that does return a Left/Top/Width/Height property.

Can you provide a little more information such as chart type, how should the
shape be positioned vertically.
And which xl version are you using?

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Trevor Williams" wrote in
message ...
Hi All

I'm trying to align a rectangle (called mySpacer) with the Left edge of
Point(x) in a SeriesCollection. The following line of code returns error
438
so I'm presuming
the 'Points' object doesn't have a Left value.

ActiveChart.Shapes("mySpacer").Left =
ActiveChart.SeriesCollection(1).Point(x).Left

Can you steer me in the right direction?
(I've looked at Jon Peltier's site but can't see anything obvoius.)

Can I make an alternative suggestion first.
Use some dummy data series to position and holder the images within the
chart.
Try this, on a blank worksheet enter the following data

B1: =Data C1: =Space D1: =Image Holder
A2: =a B2: =1 C2: =MAX($B2:$B5)-B2+1 D2: =1
A3: =b B3: =2 C3: =MAX($B2:$B5)-B2+1 D3: =1
A4: =c B4: =3 C4: =MAX($B2:$B5)-B2+1 D4: =1
A5: =d B5: =4 C5: =MAX($B2:$B5)-B2+1 D5: =1

Create a stacked column on the range A1:D5
Format the Space series to have no fill or border pattern.

Now add 4 pictures to the worksheet.
Use the following code to place a copy of each picture above a column.

'-------------------------------------------
Sub x()

Dim chtTemp As Chart
Dim shpPics(4) As Shape
Dim lngIndex As Long

Set chtTemp = ActiveSheet.ChartObjects(1).Chart
For lngIndex = 1 To 4
Set shpPics(lngIndex) = ActiveSheet.Shapes(lngIndex + 1)
Next

With chtTemp
.Parent.Activate
With .SeriesCollection(3)
For lngIndex = 1 To .Points.Count
.Points(lngIndex).Select
shpPics(lngIndex).Copy
Selection.Paste
Next
End With
End With

End Sub
'------------------------

Is this a workable approach for you?

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Trevor Williams" wrote in
message ...
Hi Andy - thanks for the quick response.

I'm using XL2002. The chart is a ColumnChart.
I need to add a predefined image above each point in the series
collection.
The images are named "pic1, pic2..." etc.

I resize 'mySpacer' by dividing the the Category Axis width by the count
of
the points in the chart. 'mySpacer' is aligned to the Value Axis Top -20.

I want to align mySpacer to the left of each point and then align the
image
centrally to mySpacer.

Here's my code to align the images...

Sub mcr_Add_Comms_Potential_Dots_To_Graphs()
Dim cell As Range

x = 1

For Each cell In Range("D38:D67")
If cell = "" Then Exit For
ActiveSheet.Shapes("pic" & x).Cut
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart
.Paste
With .Shapes.Range(Array("pic" & x, "mySpacer"))
.Align msoAlignBottoms, False
.Align msoAlignRights, False
.Align msoAlignCenters, False
.Align msoAlignMiddles, False
End With

ActiveChart.Shapes("mySpacer").Left =
ActiveChart.SeriesCollection(1).Point(x + 1).Left

End With
x = x + 1
Next

End Sub



"Andy Pope" wrote:

Hi,

You will need to calculate the position using other information in the
chart
that does return a Left/Top/Width/Height property.

Can you provide a little more information such as chart type, how should
the
shape be positioned vertically.
And which xl version are you using?

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Trevor Williams" wrote in
message ...
Hi All

I'm trying to align a rectangle (called mySpacer) with the Left edge of
Point(x) in a SeriesCollection. The following line of code returns
error
438
so I'm presuming
the 'Points' object doesn't have a Left value.

ActiveChart.Shapes("mySpacer").Left =
ActiveChart.SeriesCollection(1).Point(x).Left

Can you steer me in the right direction?
(I've looked at Jon Peltier's site but can't see anything obvoius.)

Hi Andy - I think I've cracked it using the Yaxis.left plus pointwidth *
point number...

Dim cell As Range
x = 1
a = 0

ActiveSheet.ChartObjects("Chart 1").Activate
myPointwidth = ActiveChart.Axes(xlCategory, xlPrimary).Width /
ActiveChart.SeriesCollection(1).Points.Count

For Each cell In Range("D38:D67")

If cell = "" Then Exit For
ActiveChart.Shapes("mySpacer").Left = ActiveChart.Axes(xlValue,
xlPrimary).Left + (myPointwidth * a)
ActiveSheet.Shapes("pic" & x).Cut
With ActiveChart
.Paste
With .Shapes.Range(Array("pic" & x, "mySpacer"))
.Align msoAlignBottoms, False
.Align msoAlignRights, False
.Align msoAlignCenters, False
.Align msoAlignMiddles, False
End With

End With
x = x + 1
a = a + 1
Next

End Sub

"Andy Pope" wrote:

Hi,

You will need to calculate the position using other information in the chart
that does return a Left/Top/Width/Height property.

Can you provide a little more information such as chart type, how should the
shape be positioned vertically.
And which xl version are you using?

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Trevor Williams" wrote in
message ...
Hi All

I'm trying to align a rectangle (called mySpacer) with the Left edge of
Point(x) in a SeriesCollection. The following line of code returns error
438
so I'm presuming
the 'Points' object doesn't have a Left value.

ActiveChart.Shapes("mySpacer").Left =
ActiveChart.SeriesCollection(1).Point(x).Left

Can you steer me in the right direction?
(I've looked at Jon Peltier's site but can't see anything obvoius.)