XL2002 - Help with Points in SeriesCollection

[Trevor Williams]

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.)

 

[Andy Pope]

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

 

[Trevor Williams]

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("D3867")
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]

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 A15
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

 

[Trevor Williams]

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("D3867")

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