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


"Trevor Williams" wrote in
message ...
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
------------------------

PS
I changed your
.Shapes.AddLine(335.25, 127.5, 623.25, 127.5).
to
..Shapes.AddLine(35.25, 27.5, 123.25, 127.5)

so I could see them !

Peter T

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

"Peter T" wrote:

PS
I changed your
.Shapes.AddLine(335.25, 127.5, 623.25, 127.5).
to
..Shapes.AddLine(35.25, 27.5, 123.25, 127.5)

so I could see them !

Peter T

If you mean what I think you mean, and specifically for a Line, I posted an
example how to do that -

http://tinyurl.com/yuevs2

Regards,
Peter T

"Trevor Williams" wrote in
message ...
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

"Peter T" wrote:

PS
I changed your
.Shapes.AddLine(335.25, 127.5, 623.25, 127.5).
to
..Shapes.AddLine(35.25, 27.5, 123.25, 127.5)

so I could see them !

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

"Peter T" wrote:

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


"Trevor Williams" wrote in
message ...
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
------------------------