Manipulating size, position, and appearance of Autoshapes

[bgboynton]

I'm trying to figure out how to use Excel and Autoshapes to create simple engineering drawings:
Is there a way to link the position and/or size of an autoshape (eg. length and width of a rectangle) to specific cells on the worksheet?
Is there a way to use "conditional formatting" with these autoshapes?

... Any help would be appreciated!

[Yogi Anand]

On 2002-05-09 16:52, bgboynton wrote:
I'm trying to figure out how to use Excel and Autoshapes to create simple engineering drawings:
Is there a way to link the position and/or size of an autoshape (eg. length and width of a rectangle) to specific cells on the worksheet?
Is there a way to use "conditional formatting" with these autoshapes?

... Any help would be appreciated!

Hi bgboynton:
This is avery interesting topic. I am glad you have started this thread.

As far as dimensioning the AutoShapes -- Yes! one can click on the AutoShape, right Click on one of the handles and Format AutoShape Dialog box pops up that facilitates sizing, positioning, line colors, fill colors,... an d so on;

However tying the dimensions of the AutoShape to the values of some cell contents -- not directly -- I wonder whether some Excel GURUs can confirm that perhaps it can be done via VBA.

Conditional Formatting -- No!

I myself am very much interested in exploring this further, for say, Bending Moment and Shrar Force Diagrams, Slopes, Deflections, ... for analysis

Component Drawing and dimensioning for Design work, ....

Please look at MrExcel's general information. MrExcel consulting has an Engineering Division headed by Mala, who has presented a number of engineering drawings which are accessible as a hyperlink from MrExcel's -- however other than this bit of information, I am not sure how Mala produces scaled drawings and shapes.
I hope Mala will accept this request to provide valuable and insightful comments on this issue from first-hand experience.

Regards!

[Tom Schreiner]

This may be an over-simplification of what you need...
This will add a rectangle, name it Rec3. Then
place it and size it according to the values
in A1:A4

Sub MakeRec()

Dim ThisShape As Shape

Dim ShapeName As String

ShapeName = "Rec3"

ActiveSheet.Shapes.AddShape(msoShapeRectangle, 282.75, 149.25, 71.25, 46.5). _

    Name = ShapeName



Set ThisShape = ActiveSheet.Shapes(ShapeName)



With ThisShape

    .Left = Cells(1, 1)

    .Top = Cells(2, 1)

    .Height = Cells(3, 1)

    .Width = Cells(4, 1)

End With

   

End Sub

Tom

[Tom Schreiner]

P.S.
As Mr Formula already stated, you cannot use Excel's conditional formatting. However, you can create your own procedure to manipulate the properties of any autoshape based upon whatever conditions you desire.
Tom

[Yogi Anand]

Hi Tom:
Thanks for getting us started on the right footing -- and now perhaps we can further build on it.

Regards!
Yogi Anand