XL2002 - Select All Objects in an Array...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have some code that draws a variable amount of lines to a worksheet. Each
time a line is drawn it is given a sequential name (myShelf1, myShelf2...
etc) and the width of the line changed to a value specified on the worksheet.

Here's what I need...
1. I need to be able to start the lines at the edge of a pre-defined
rectangle called myFixture.

2. I need to be able to select all lines that are called myShelf*, plus 2
other pre-defined lines (myShelfTop & myShelfBottom), and then distribute
them vertically.

As usual, any help greatly appreciated.

my code so far...

----------------------------
Sub AddShelves()

'clear current shelves
For Each shp In ActiveSheet.Shapes
If shp.Name Like "myShelf*" Then
shp.Delete
End If
Next shp

'add new shelves
For i = 1 To Range("D17").Value 'this is how many lines to add
With ActiveSheet
.Shapes.AddLine(335.25, 127.5, 623.25, 127.5).Name = ("myShelf") & i
.Shapes("myShelf" & i).Width = Range("myLength")
End With
Next i

'distribute shelves - ShelfTop & ShelfBottom are predefined
ActiveSheet.Shapes.Range(Array("ShelfTop", "ShelfBottom", "myShelf1",
"myShelf2", "myShelf3")) _
.Distribute msoDistributeVertically, False

End Sub
------------------------
 
Hi Trevor,

It seems rasther a haphazard way of positioning your shapes, would have
thought you could set your positions in the loop in the 'AddLine' arguments.
Anyway, have a go with this -

Sub AddShelves()
Dim shp As Shape
'clear current shelves
For Each shp In ActiveSheet.Shapes
If shp.Name Like "myShelf*" Then
shp.Delete
End If
Next shp

' ought verify ShelfTop & ShelfBottom exist
ReDim arr(0 To Range("D17") + 1)
arr(0) = ActiveSheet.Shapes("ShelfTop").ZOrderPosition
arr(1) = ActiveSheet.Shapes("ShelfBottom").ZOrderPosition

'add new shelves
For i = 1 To Range("D17").Value 'this is how many lines to add
With ActiveSheet
With .Shapes.AddLine(35.25, 27.5, 123.25, 127.5)
.Name = ("myShelf") & i
.Width = Range("myLength") ' why this and not in 'AddLine'
arr(i + 1) = .ZOrderPosition
End With
End With
Next i

'distribute shelves - ShelfTop & ShelfBottom are predefined
'ActiveSheet.Shapes.Range(Array("ShelfTop", "ShelfBottom", "myShelf1",
"myShelf2", "myShelf3")) _
.Distribute msoDistributeVertically, False
ActiveSheet.Shapes.Range(arr) _
.Distribute msoDistributeVertically, False
End Sub

Changing respective ZOrderPosition's and place in the array may give
different results

Regards,
Peter T
 
Hi Peter - thanks for your response, it works a treat.

It makes sense to define the Width of the line in the AddLine statement, so
I'll get cracking on that.

Thanks agin.

Trevor
 
Hi Peter

Do you know how to return the Begin x, Begin y, End x, End y values of an
object that already exists on a sheet?

Thanks

Trevor
 
Back
Top