code to draw rectangle shape ... size based on table data

[jammoca]

I have 24 data values in the range ... C6:C29

I would like some code that would draw 24 rectangles (one for each data value).

The rectangles would have the 3D formatted appearance called ... Intense Effect - Blue, Accent 1

Each rectangle would be 0.5 centimetres wide (vertically).

The length (horizontally) of each rectangle would be the length (in centimetres) found in its corresponding cell.

For example ...

the rectangle for cell C6 would be 0.5cm wide and 1.34cm long ... as cell C6 has the value 1.34 in it.

the rectangle for cell C7 would be 0.5cm wide and 1.02cm long ... as cell C7 has the value 1.02 in it.

I'm guessing these rectangles would need to be all drawn in a new sheet, perhaps called 'Horse Race Rectangles'.

Is this possible ?

Kind regards,

Chris

 

[jdesi1]

Re: code to draw rectangles

That worked very well, but may I ask for three amendments ?

1) is it possible to have them drawn in centimetres, not inches ... 0.5cm high, not 0.5 inches high, and the cell data is in cm as well, not inches.

2) I actually have 9 columns of data, not just the one I mentioned in the brief. Is it possible to have 9 separate sets of 24 rectangles drawn .... a set of 24 rectangles for the data in C6:C29, a separate set of 24 rectangles for the data in D6:D29, etc etc etc all the way up to K6:K29.

3) Is it possible to have the rectangles drawn on a separate sheet .. one called ... Horse Race Rectangles

You've definitely created what I'm looking for, but I can't figure out how to adjust your code myself, my apologies.

***Moderator - please merge if you prefer - I wanted to keep the reply with the relevant questions. Thanks.

1) Sorry, my hemisphere is showing (it's late & I blew right past the units). I adjusted inches to cm.
2) I adjusted & tested for 3 columns of data -- update the code below to fit your needs...
3) I added a worksheet named as you suggested -- the rectangles are drawn there adjacent to each other, as you explained in the other thread.

Again, this is quick & dirty, variables are not declared, no error checking, etc. You can mess with all that as you like.



Sub Draw72RectanglesAlt()
'72 = 1 inch; divide by 2.54 to adjust for cm

startx = 300
starty = 20
dy = 36 / 2.54


For zrow = 1 To 24

currentx = startx
For zcol = 1 To 3 'tested with 3 columns; change 3 to 9

dx = Cells(zrow + 5, zcol + 2) * (72 / 2.54)
Worksheets("Horse Race Rectangles").Shapes.AddShape(msoShapeRectangle, currentx, starty + (zrow - 1) * dy, dx, dy).Select
currentx = currentx + dx

Next zcol

Next zrow

End Sub

 

[jammoca]

Re: code to draw rectangles

That worked brilliantly, thankyou.

Now, I have one more question that, I suspect, will really stretch the friendship.

If we just consider the rectangles for C6:C29 ... let's call the rectangle for C6 as Rectangle A, the rectangle for C7 as Rectangle B, etc etc etc, all the way up to the rectangle for C29 as Rectangle X.

Is it possible to code it so that all the rectangles are drawn, not one under the other, as they currently are, but side by side, from left to right, so that, for example, rectangle B starts where rectangle A finishes, rectangle C starts where rectangle B finished, etc etc etc. We know how long each rectangle is (that information is in the cells), so could we use that knowledge to tell each rectangle where to begin ?

Of course, that would place all the 24 rectangles from the range C6:C29 in a horizontal format, with rectangle A on the far left and rectangle X on the far right.
I'd then need the same done for the rectangles that refer to D6:D29, and E6:E29 etc etc etc all the way up to K6:K29.

In the end, it would look like a 'stacked bar chart', but I don't want to simply plot my data in that format, as I need each of the rectangles to be able to be individually manipulated later.

 

[jdesi1]

Re: code to draw rectangles

That worked brilliantly, thankyou.

Now, I have one more question that, I suspect, will really stretch the friendship.

If we just consider the rectangles for C6:C29 ... let's call the rectangle for C6 as Rectangle A, the rectangle for C7 as Rectangle B, etc etc etc, all the way up to the rectangle for C29 as Rectangle X.

Is it possible to code it so that all the rectangles are drawn, not one under the other, as they currently are, but side by side, from left to right, so that, for example, rectangle B starts where rectangle A finishes, rectangle C starts where rectangle B finished, etc etc etc. We know how long each rectangle is (that information is in the cells), so could we use that knowledge to tell each rectangle where to begin ?

Of course, that would place all the 24 rectangles from the range C6:C29 in a horizontal format, with rectangle A on the far left and rectangle X on the far right.
I'd then need the same done for the rectangles that refer to D6:D29, and E6:E29 etc etc etc all the way up to K6:K29.

In the end, it would look like a 'stacked bar chart', but I don't want to simply plot my data in that format, as I need each of the rectangles to be able to be individually manipulated later.

It would be best if you go through the code snippet and understood what each piece does, then you can adjust to your exact needs. The currentx = currentx + dx bit starts the next rectangle at the end of the previously created one. Beyond that and some basic math, the For loops and "AddShape" do all the work. For loops are nested, for 3 columns x 24 rows, keeping track of the x, y, dx and dy values. If you can understand the arguments to AddShape, you should be able to work out the rest.

 

[Michael M]

Re: code to draw rectangles

just reply to your own thread...and type BUMP.
It then goes to the top of the list and people will see it again.

By starting a new thread, in this case it doesn't matter, but people may have answered the original thread and you now possibly have other people answering a thread that may have been solved in the original....does that make sense !!!

 

[jammoca]

Re: code to draw rectangles

OK< we're so close now.

Using the following code ..

Sub Draw72RectanglesAlt()
 '72 = 1 inch; divide by 2.54 to adjust for cm

 startx = 300
 starty = 20
 dy = 36 / 2.54


 For zrow = 1 To 24

 currentx = startx
 For zcol = 1 To 3 'tested with 3 columns; change 3 to 9

 dx = Cells(zrow + 5, zcol + 2) * (72 / 2.54)
 Worksheets("Horse Race Rectangles").Shapes.AddShape(msoShapeRectangle, currentx, starty + (zrow - 1) * dy, dx, dy).Select
 currentx = currentx + dx

 Next zcol

 Next zrow

 End Sub

we currently have my rectangles from C6:C29 displaying down the screen, and the rectangles for D6:D29 also displaying down the screen and butted up against the C6:C29 rectangles etc etc etc, however, I was wanting the C6:C29 rectangles to display horizontally butting up to eachother left to right .. rectangle A butts up against rectangle B, which butts up against rectangle C etc etc etc.

I then needed the rectangles for D6:D29 to also display horizontally below the C6:C29 rectangles, and the E6:E29 rectangles to display horizontally below the C6:C29 rectangles, etc etc etc

In other words, something like the following ..

C6 rectangle up against C7 rectangle up against C8 rectangle up against C9 rectangle etc etc etc etc
D6 rectangle up against D7 rectangle up against D8 rectangle up against D9 rectangle etc etc etc etc
E6 rectangle up against E7 rectangle up against E8 rectangle up against E9 rectangle etc etc etc etc
ETC
ETC
ETC

how would I re-write the code to do that ?

 

[jdesi1]

Re: code to draw rectangles

I added some comments to make it clearer - this goes in the order specified - again, I only did 3 columns, adjust the "zcol = 1 to 3" to match your # columns.

Also - this code expects all data to be contiguous (no blank columns). I ran it with blanks accidentally, did not try zero values -- either way, it would draw a rectangle of width 0 / wouldn't break.


Sub Draw72RectanglesAlt2()

'define variables to be used below
'72 = 1 inch; divide by 2.54 to adjust for cm
startx = 300 'x-coordinate for starting rectangle - adjust as needed
starty = 20 'y-coordinate for starting rectangle - adjust as needed
dy = 36 / 2.54 'rectangle height 1/2cm


currenty = starty 'initialize current y-coordinate

'this codes loops by column, with a nested loop to process all rows in each column

For zcol = 1 To 3 'change 3 to however many columns exist (must be contiguous data)
currentx = startx 'initialize current x-coordinate to startx for each column loop
For zrow = 1 To 24
dx = Cells(zrow + 5, zcol + 2) * (72 / 2.54) 'calculate rectangle width
'add shape (x-coordinate, y-coordinate, width, height)
Worksheets("Horse Race Rectangles").Shapes.AddShape(msoShapeRectangle, _
currentx, currenty, dx, dy).Select
currentx = currentx + dx 'set new x-coordinate for start of next rectangle
Next zrow
currenty = currenty + (2 * dy) 'set y-coordinate for start of next row (skip one rectangle height - adjust "2*dy" as needed)
Next zcol


End Sub

 

[Joe4]

Re: code to draw rectangles

I have merged your two threads together.

In the future, please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule # 12 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html).

Rule #12 there and #7 here talk about bumping: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

Thank you.