Draw triangle that changes size depending on cell values.

**alasdairfraser** · 07-17-2010, 07:32 PM

Wouldn't know what VBA was if it knocked down and killed me, lumbered with Openoffice at home, so, if at all possible, could you post an worksheet with the solution embedded? I am genuinely sorry, and will learn VBA, just not this weekend.
----------
Excel 2000; w2000p
---------
I'd like to draw a right-angled triangle, anchored [not changing position within the worksheet] on the right angle, which changes side length depending on cell values entered for two or three of [height, base, hypotenuse].
So, for values 120 and 20, in cells a1 and a2, need to produce a 120*20 pt triangle, area 1200sqpts.

I am aware of ways to do something similar using conditional formatting, but it's the triangle I need for a printed infographic.

I am also aware that you can resize a triangle manually/use charts. However this is impractical given that the output is a diagram with a grid of 48 triangles, expected to alter fast depending on variables elsewhere in the spreadsheet, and charts need resizing anyway to preserve scale.

Many thanks for your time.

**mikerickson** · 07-17-2010, 08:39 PM

How about this?

Enter the horizontal and vertical sides in A1 and A2.
(The length of the hypotenuse is automatically calculated in A3)
Enter the scale of the drawing in A5.
Press the button and the triangle will be drawn by this sub

Sub makeTriangle()
    Dim Horiz As Double, Vert As Double
    Dim cornerTop As Double, cornerLeft As Double
    On Error GoTo Halt
    With ThisWorkbook.Sheets("Sheet1")
        Horiz = Val(CStr(.Range("A1").Value)) * Val(CStr(.Range("A5").Value))
        Vert = Val(CStr(.Range("A2").Value)) * Val(CStr(.Range("A5").Value))
        cornerTop = .Range("D2").Top
        cornerLeft = .Range("D2").Left
        
        With .Shapes("Line 1"): Rem horizontal
            .Top = cornerTop
            .Left = cornerLeft
            .Height = 0
            .Width = Horiz
        End With
        
        With .Shapes("Line 2"): Rem vertical
            .Top = cornerTop
            .Left = cornerLeft
            .Height = Vert
            .Width = 0
        End With
        
        With .Shapes("Line 3"): Rem hypotinuse
            .Top = cornerTop
            .Left = cornerLeft
            .Height = Vert
            .Width = Horiz
        End With
    End With
Halt:
    On Error GoTo 0
End Sub

**alasdairfraser** · 07-18-2010, 11:24 AM

Many thanks for this. I've had a play around, and while it's great for drawing one triangle, it doesn't seem to want to draw two.

The two ways I've tried it are:
1)
Adding a separate macro, Maketriangle1 (), and executing in a group macro. In this instance, it leaves only the three lines with the three lowest x values [1,2,3] of the .Shapes("Line x") arguments.

The funny thing is, you can see Excel drawing both triangles before it wipes one away. I've tried messing about with the Static command, but it still leaves just one triangle drawn.

I renamed all of the defined variables and cell references. Can the .Shapes() command only draw one thing at once?

2)
Same as above, but adding more commands under the same macro. Now it draws nothing!

Also, is there any way to add a fill color/colour to the shape?

Many thanks for your help.

**mikerickson** · 07-18-2010, 01:15 PM

Excel is not a graphics package. If you have a fixed number of triangles, they can be created with the Draw capabilities. But if for creating and moving them on the fly, Excel is not the program for the task.

**alasdairfraser** · 07-19-2010, 04:53 PM

I'm not wanting to move them, or change the number of them at on. I want to alter their sizes on the fly [I have Illustrator for drawing things with].

My problem is just with creating a fixed number of triangles greater than one. I haven't been able to draw two working triangles given my adaptations to the code, kindly supplied previously.

Many thanks,

**mikerickson** · 07-19-2010, 05:32 PM

If you could post a copy of your workbook, it might help me understand what you are looking for.
If you have existing triangles, calculating the coordinates of the end-points is pretty straight forward.
Applying those coordinates to the triangles is heavily dependent on how your triangles are made (3 line segments or a closed curve or ... ) and how they are named.

**alasdairfraser** · 07-19-2010, 07:04 PM

Thanks, attached.

**lecxe** · 07-19-2010, 10:23 PM

Hi

To draw a right triangle you can also use the RightTriangle shape.

For ex.:

Sub DrawRightTriangle(sName As String, dLeft As Double, dTop As Double, dWidth As Double, dHeight As Double)
Dim shp As Shape

Set shp = ActiveSheet.Shapes.AddShape(msoShapeRightTriangle, dLeft, dTop, dWidth, dHeight)
shp.Name = sName
End Sub

A test to create your 120*20 triangle

DrawRightTriangle "RightTriangle01", 50, 100, 20,120

You can use a loop that calls DrawRightTriangle to create your 48 triangles.
You know their name so they will be easy to manipulate.

To fill the shape with a color you can add to the procedure, for ex.:

shp.Fill.ForeColor.RGB = RGB(255, 0, 0)

**rosswell** · 08-28-2012, 07:23 AM

Hi folks!

Long time since the last entry, but anyway...
I'm new here and have been following the thread, because I pretty much need to automatically draw a triangle as the original request of the post.
There are some differences though:
- I just need 1 triangle per macro (or maybe two, but I guess that's not important right now).
- It's not a right triangle. It's a oblique triangle, and I have all the length values, no need to write formulas.

I have downloaded the "workbook1" that mikerickson gently posted, and I thought I could easily transform it to my needs, but I'm not going any further (Oh yeah, I'm totally new to vba, but I can follow some logical instructions...). Here are the difficulties I found:
- I was not able to change the "orientation" of the triangle, if you know what I mean. As the original triangle from the post kind of has an orientation which begins at the Top/Left corner and grows to the right and to the bottom, my triangle has its origin in the Bottom/Left corner, and grows to th right and to the top. I've attached and image which I hope will help you understand.
veltri.JPG

So, basically, that's my problem, because I'm not able to "change the origin" of the triangle. I've tried some weird lines like "corner.Bottom" and stuff, but I guess that doesn't exist, right....

I'm sorry for my unappropriate terms, but I would really appreciate a help or a hint on what should I do.
Thank you!

**arlu1201** · 08-28-2012, 07:35 AM

Rosswell,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

**rosswell** · 08-28-2012, 08:12 AM

Hi Arlette,

I'm really sorry for the mistake. I posted in the same thread because I didn't want to make a fuzz about it, hehe..
Sorry, won't happen again. What will happen, on the other hand, is me reading the forum rules properly...

Cheers!

**mikerickson** · 10-26-2013, 03:04 AM

In reply to the request for a trapezoid version of this:

Note the position and size entries in columns J and K.

**riz4u** · 04-20-2016, 01:08 PM

Dear all i want to create a free from or polygon shapes by cell inputs.as the values enters it create shape as per cell measurments in inches or cm .i did it by hiding/showing pics.but i want to draw a true shape as per given values to see effect.please view excel sheet for understanding

http://www.excelforum.com/attachment...7&d=1461123964

**protonLeah** · 04-20-2016, 05:10 PM

riz4u,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**riz4u** · 04-20-2016, 07:15 PM

DEAR I AM NEW I DIDNT GETTING RIGHT RESPONSE. I POST MY ARTICLE BUT IT DISAPEAR .http://www.excelforum.com/attachment...7&d=1461123964

**6StringJazzer** · 04-20-2016, 11:08 PM

riz4u, your post did not disappear. It is here.

In your post above, you have pasted a truncated link. I am not going to get into a big explanation of what that means here, seeing as though you have violated the hijacking rule. Please continue in your other thread, not this one, as protonLeah has pointed out.

Draw triangle that changes size depending on cell values.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Re: Draw triangle that changes size depending on cell values.

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions