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

 

[Michael M]

First question.....why ???
Putting a dynamic number of shapes on any sheet will start to bloat your file size very quickly !!
What are you actually trying to achieve with these......not a form of Gantt chart ???

 

[jammoca]

With these rectangles, I copy them across to Powerpoint and manually align them to sit one beside the other ... the second rectangle begins where the first one finishes, the third one begins where the 2nd one finishes, etc etc etc, so they appear as one seamless long rectangle.

I then assign an animation effect to each rectangle so they each wipe (over a period 0f 2 seconds) from left to right.

This then gives the effect that the bar is growing (from left to right) at changing speed.

In reality, I then have 8 other groups of these all appearing on the same powerpoint under each other. Each of the 9 groups of rectangles represents the points achieved by 9 different sporting clubs as the season progressed.

In essence, the final effect is to display the progress of the 9 sporting clubs as the season progressed ... I call it a 'horse race' display.

 

[jammoca]

code to draw rectangles

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).

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'.

I am not trying to draw a Gantt Chart.

Is it possible to code for what I'm trying to do ?

 

[jdesi1]

Re: code to draw rectangles

This draws rectangles right in place for each of the 24 cells. There are more elegant ways to do this, but this is a start.

Sub Draw24Rectangles()
'72 = 1 inch
'set starting x value
startx = 300
'set starting y value
starty = 20
'height = .5 inch (.5*72->36)
dy = 36


For Z = 1 To 24
'get numeric value for width (multiply cell value by 72)
dx = Cells(Z + 5, 3) * 72
'add rectangle (x, y, width, height)
ActiveSheet.Shapes.AddShape(msoShapeRectangle, startx, starty + (Z - 1) * dy, dx, dy).Select
Next Z


End Sub

 

[jammoca]

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.

 

[Michael M]

Re: code to draw rectangles

You probably should stick to the original thread....duplicate posting is frowned upon as per the forum rules....Rule #12

See here

http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html


Duplicate post here

http://www.mrexcel.com/forum/excel-...aw-rectangle-shape-size-based-table-data.html

 

[jammoca]

Re: code to draw rectangles

Hi Michael,

I have always stuck to the thread rules, but when you responded to my original thread you didn't provide a solution to the problem. You simply queried why I would want to do what I queried.

This, as you know, puts a 1 in the 'replied' column, which often means no one else will look into my query, thinking the query has been solved due to the reply.

I waited several hours, and no further assistance was given, so I had to re-submit the query.

 

[Michael M]

Re: code to draw rectangles

All you had to do was BUMP your own thread !

 

[jammoca]

Re: code to draw rectangles

how do you bump a thread ?