Autoshape Automation

[surya prakash]

Hi,
I am wondering if the figures shown in the attachement can be controlled either by using autoshape or a chart.

When the user changes few variables given in form of D, B, fp1, fp2 .... etc, the drawing should be updated accordingly....

Thanks

[Stead]

Hi, i might be faced with a similar problem too, i've been thinking about it, i've discovered you can set macro's to function when a user clicks on the shape, but i don't have the time to look into it yet, however in a few days i might have time, I did have a quick look however and...

...i've made a little spreadsheet, basically you enter the Length And Height, and it'l make a triangle, not sure if it'll be any use, its very hard to work out all these xy's on teh shapes i thought, but it made me think away from what i've been stuck on for the past few days!:thumb

it allso adds nice little double ended lines

I figured it may help build more complex shapes, theres also a nice button that rmoves all the lines on spreadsheet, which is very useful, as i was deleting all the lines manwelly for ages, well if its no use, i think its neat anyway!

[surya prakash]

Hello Stead,
Thank you for your response.
I am wondering if we can use chart for generating the drawing?

Thanks

[Stead]

I've played around with the charts, what chart are you referring too?

after playing around it seems the only way to me is to use lines to make the shapes, i've written a small test that makes the triangle like in the sheet i attached, then group it to resize it easier, but if the shape has to be auto generated may as well generate it each time.

Hrmm Actually thinking about it, if all the shapes are going to be the same, you could create the autoshapes using the lines, group them, add them to the autoshape collection? ( think you can do this) then when the user puts in the dimensions, draw the shape according to the dimensions given using the autoshapes that you just created?

[Bob Phillips]

No answer for this, it doesn't look simple. But it does occur to me that this is better suited to Visio, if you have that product.

[TonyJollans]

Looks like a bit of fun.

The drawings could be made with Excel Shapes and updated when the cell values change.

Some questions:
1. How far from the left and right edges are the bolts - i.e how do you calculate p?
2. How far from the top and bottom edges are the bolts - is B equal to ((number of bolts / 2) * (bolt diameter)) + ((number of bolts + 1) * g)
3. What is x in the bottom diagram?
4. Is D in the bottom diagram the same as D in the top diagram?

[Aaron Blood]

If it were me, I'd create a single custom shape and then modify the array of vertice data to adjust the drawing appropriately.

I have an example on my website that shows some VBA used to draw custom autoshapes... you might find it useful.

Look for the drawing autoshapes example in the VBA section.
www.XL-Logic.com (http://www.XL-Logic.com)

[surya prakash]

Many Thanks Guys for responding to my post....

Stead: I have seen a similar shape being generated using Chart; but I dont know what kind of chart he has used. the figure gets updates the moment user changes some values.

Tony: P = g =100; Distance from the edge to the bolts center = 50;
X in the bottom diagram can be ignored.
D in the both the figures is same.
How would one update the figure, when the value in the cell changes??

[excelliot]

It is diifiicult ?
let me try....:whistle:

[Stead]

Sorry for the long delay with a reply, been a little busy!

i'm still confused, when you say chart, do you mean as in a chart in excel?

Well here's a little test i did, (i am quite tired, so i'm not sure if it'll be any use)
anyway, its a chart, that somehow makes up a nice shape, there are 5 lines although you only see 4, i tried atriangle first and the only way i could do it was to use 4 lines, not sure why,but it seems to work, however how to get it into a simple just change one number for it all to change kinda way?

Each line has to start and end someware, so i guess when you change one, it would change the start of the next line, but then the same principle would go with the autoshapes, unfourtantly i am no pro programmer so i don't think i can think of anything else, currently nearly all my excel knowledge is self taught trying to write a system in excel to manage a small company, and hopefully i can get a job out of it at the end of the day! I'm guessing that type of thing looks impressive on a cv? anyway enough of my rant :p i dare say i'll be curious and look again over the weekend, i do find it the idea very interesting, and i wouldn't say it can't be done, just, there is no easy answer (but in my mind if something is uncommon, and you do it, it just means you get to be extra proud because you know no one else has done it, or at least very few ;))

[Justinlabenne]

Just from what I can see, what your asking for is do-able, but probably not without soem serious time involved. Being able to draw some shapes and some really cool things with x/y charts is not so complicated after you have spent hours learning it, but it still has limitations without a serious run down of parameters, what things will change, how the bolt locations will change, if bolts are added, there are just so many factors involved in this that it will most likely take a great deal of time to get right.

To help on your quest to achieveing this, this link has a lot of extremely creative charts that are close to your project requirements. All the workbooks are available for download, but some vba projects are locked, but most are open source. See if you can find something useful

Cool Charts (http://www.mrexcel.com/pc11.shtml)

[surya prakash]

Hello Stead: Thanks for the Excel File; I would try and improve on what you have done. But one question: How would you bring in circles in the same chart?

Justinlabenne: Thank you for the link; I was looking for the exact thing. My application is Engineering diagrams using autoshapes or Chart as mentioned in http://www.mrexcel.com/graphics.shtml

But the problem is I am not able to download any of these charts to study them?

[Justinlabenne]

The charts on my link you can download. The charts you see on your link are done by professional consultants, and who knows how much they got paid to do them. :wot

What your after is not easy by any means (at least for me) and I am sure the complexity and hours spent by the consultant doing the engineering diagram charts is a pretty good reason why they are not available for download to study.

Hopefully some of the stuff provided for you here will help you to get what you need, but It will probably take quite a bit of trial and error and study on your part. This solution may need a monetary investment, whether in some other software, or consulting.

Heres other links to study:

Jon Peltier (http://peltiertech.com/Excel/Charts/ChartIndex.html)

OzGrid (http://www.ozgrid.com/Services/ExcelChartTools.htm)

[TonyJollans]

Prakash,

The basic code to draw/redraw is not particularly difficult but is quite long winded. I will post a partial sample later today but please confirm:

p = g = 100 regardless of other (changeable) measurements. This will impose constraints on the other values.

[Stead]

I'm not sure if you could bring circles onto the chart...i was thinking about it for a bit,but the only way i can think of is calcuating the position and adding an autoshape, but that could be done using the values given in the chart?, a bit of trial and error would be required tho i would of thought.

The only problem with that idea i believe, is ... well i've noticed in most programs the shapes like to go behind other things, if excel lets you put the autoshapes above the chart then it could work...maybe!

i just had quick link on those sites, and the first one states it all done using autoshapes...

[TonyJollans]

A bit later than promised.

The attached example redraws pictures when you press the button. I like Aaron's idea of just changing the vertices and I might look into that. Meanwhile this is an unfinished work - the code is pretty messy and short of quite a few bits but it should show one way of doing it. Just enter the measurements in the cells and press the button.

[Stead]

I know this isn't my problem, but i'm very impressed!

I hope you get paid a lot for what you do!

[MOS MASTER]

A bit later than promised.

The attached example redraws pictures when you press the button. I like Aaron's idea of just changing the vertices and I might look into that. Meanwhile this is an unfinished work - the code is pretty messy and short of quite a few bits but it should show one way of doing it. Just enter the measurements in the cells and press the button.

Very nice Tony! :yes

[johnske]

Very nice Tony! :yesYes, nyce indeed... :yes :thumb

[surya prakash]

Sorry guys, I was not well and couldnt respond earlier.
Please give me a day to catch up with things and revert back to you.
Many thanks to all of you for responding to my post....

[surya prakash]

Many thanks Tony for the timely help...
Thank you very much the chart links Justin Labenne....


My post is resolved.....