automate drawing rounded rectangles to circle data

[jerry12302]

I have trending data and would like to circle certain ranges of data that meet certain criteria. I have code that predefines the cell ranges that need to be circled, but I don't know how to automate the drawing of the rounded rectangles so that they circle specific cells.

I would like them to be exactly uniform in width, and for the corners to be fully rounded. The lengths would vary based on how many cells need to be circled.

In my example, D2 through F2 should be circled, and C3 through E3. Also, some data is vertical, as shown, and I would want B9 through B12 circled in the example.

Assume the cells defining the first and last cell are defined, how do I write code to automate the circling of the data with rounded rectangles?

Book1
	A	B	C	D	E	F	G	H
1
2		10	20	35	37	48	50	60
3		17	40	50	60	66	45	67
4
5		10
6		20
7		45
8		34
9		60
10		70
11		80
12		90
Sheet1

 

[RalphA]

Jerry:

Since, apparently, no one has answered your post, may I suggest that you let Excel write your macro, then you can Edit it, copy it, and put it in your code area.

I hit the Record Macro butto, used Shift R (uppercase R) , chose a "rounded rectangle" from the Draw menu, clicked on the top-left corner of cell D2, stretched the rectagle o the lower-right corner of F2, clicked on the Color fill, chose No Fill, clicked on the rectagle to un-select it, and closed the macro. I erased the visible rectangle, and pressed Ctrl+Shift+R, and the rounded-corner rectangle was back.

Now,the macro is specific o the cells where it was drawn, but, if you know VBA, youshould be able o change the code to a range specified by you, somehow. Me, I don't do VBA, so, I cnnot help you farther.

Good luck!

 

[NateO]

Hi Jerry,

The basic thing you want to do is to anchor a shape to the Range you want, so here's an example of doing this:

Sub foo()
Dim shp As Shape
 
Application.ScreenUpdating = False
 
With Worksheets(1).Range("D2")
    Set shp = .Parent.Shapes.AddShape(msoShapeOval, _
        .Left, .Top - .RowHeight / 4, _
        .Width * 3, .RowHeight * 1.5)
End With
 
With shp
    With .Fill
        .Visible = msoFalse
        .Transparency = 0#
    End With
    With .Line
        .Weight = 1.25
        .ForeColor.SchemeColor = 10
        .BackColor.RGB = RGB(255, 255, 255)
    End With
End With
 
Application.ScreenUpdating = True
 
Set shp = Nothing
End Sub

I always use a With Statement when I do stuff like this, because it's easier to refer to the Range that way. I use .Parent as you need to refer to a Worksheet, and the Parent of D2 is just that, a Worksheet.

The type of Shape you use is trivial, turn on your Macro recorder, insert the type of Shape you want, poach the name and substitute it for what I have done in my example. You can also change the Line colours, etc... In your recorded macro (unless you're using Excel 2007 - grrr).

The real trick is to change the Top, Left, Width and RowHeight to your tastes when you insert the Shape. This is an easier task if your column widths are consistent, as I'm simply taking the width of D2, in this example, and multiplying by 3 to get out to F2.

This works as expected in Excel 2002, but not Excel 2007, it's not transparent. I have an outstanding question about this already and will follow up when I know more.

 

[NateO]

J-Walk set me straight on this, if you remove the Transparency Property toggle in the code, it works in both Excel 2002 and Excel 2007, i.e.,

Sub foo()
Dim shp As Shape

Application.ScreenUpdating = False

With Worksheets(1).Range("D2")
    Set shp = .Parent.Shapes.AddShape(msoShapeOval, _
        .Left, .Top - .RowHeight / 4, _
        .Width * 3, .RowHeight * 1.5)
End With

With shp
    .Fill.Visible = msoFalse
    With .Line
        .Weight = 1.25
        .ForeColor.SchemeColor = 10
        .BackColor.RGB = RGB(255, 255, 255)
    End With
End With

Application.ScreenUpdating = True

Set shp = Nothing
End Sub

In my defense, neither one of us are quite clear on why that would matter as the Fill shouldn't be visible, but that is the culprit.

 

[NateO]

One more followup, I think this is a bug in Excel 2007. If you take the following in Excel 2007:

    With .Fill
        .Visible = msoFalse
        .Transparency = 0#
    End With

And you flip Visible/Transparency, i.e.,

    With .Fill
        .Transparency = 0#
        .Visible = msoFalse
    End With

Now it also works as expected. I.e., by toggling the Transparency Property after you toggle the Visible Property, you actually overrride/cancel-out the Visible Property.

There's no need to even touch the Transparency Property, so don't, but that is what appears to be happening in Excel 2007 (and not Excel 2002).

 

[jerry12302]

Thanks Nate, that is really cool, I appreciate the work involved and the tips involving Excel 2007, I have 2003 at work and 2007 at home (go figure). It's good to know some tricks when using files in both versions. Great post!

 

[NateO]

You're welcome!

This really threw me for a loop, so kudos to J-Walk for getting me going in the right direction (in a non-trivial way). This gives me something to think about with '07 in the future:

1. Test, test, test
2. Watch your order of operations

That property should hold its value, but it doesn't. Could that apply to other Shapes? ChartObjects? Hard to say, but I'll keep it in the back of my head. Glad I tested this in both versions.

 

[vrilya]

Hey I just wanted to tell you guys how much this information has been useful for me! Thank you very much!

 

[NateO]

You're welcome. Welcome to the board!