graphically scaling dimensional arrows

Posted by Jason on July 18, 2000 11:38 AM

I have written (with some great pieces of advice from ryan) a pretty complex excel setup that automatically determines optimum layouts for circuit boards and then draws them out by manipulating the cell sizes. It is working great with one exception, I cannot get the drawings (made by expanding/shrinking a cell and applying borders) to show dimensions. I have tried concatenating the value I need between the Symbol font's characters of the left and right arrow. But I can't get it to appropriatly scale. For any set of steps it would highlight a calculated number of cells in a row, merge them into one large cell, place hard borders on the left and right sides, and ideally fill in the value with arrows stretched until they touch the side borders.

Currently, mine will do this:

| &LT;- 35.43 -> |

But I am looking for something closer to this:

|&LT;------ 35.43 -------->|

The sizes of the cells change pretty dramatically, and my first idea of having the value and one arrow on each side and a huge font size and telling the cell to scale to fit looks ridiculous when the cell is large.

Any ideas would be greatly appreciated.

Jason

Posted by Jason on July 19, 0100 7:28 AM

That's very close to exactly what I was looking for. Would there be a way to modify it slightly? I would like to have the value of a certain cell (let's say B1) be in the middle of the dashed line. If that isn't possible, could a variable (like X) be placed in the middle so I could show the value of the specified length?

Also, I am trying to set this up automatically so it will redraw my figures for any changes to figures on another page. For instance, sometimes I need to dimension three cells across, and sometimes it's four. Would there be a way where a new arrow could overwrite the previous arrow?

Thanks so much,

Jason

Posted by AB on July 18, 0100 2:24 PM

Well, I don't know if this is what you had in mind but...

Assuming you have a cell or range of cells selected this little macro will merge the range (or keep it merged), format the contents of the selection as bold/centered, and then draw a connector to the edges with arrows.

Sub Dimensions()
With Selection
h = .Height
t = .Top
w = .Width
l = .Left
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
.Font.Bold = True
End With
x1 = l
y1 = t + (h / 2)
x2 = w
y2 = 0
ActiveSheet.Shapes.AddConnector(msoConnectorStraight, x1, y1, x2, y2).Select
With Selection.ShapeRange.Line
.BeginArrowheadStyle = msoArrowheadOpen
.EndArrowheadStyle = msoArrowheadOpen
.DashStyle = msoLineDash
.ForeColor.SchemeColor = 23
End With
End Sub

-Aaron :)