Report Card-like format

  • Thread starter Thread starter Tod
  • Start date Start date
T

Tod

I don't know the best NG for this questions, so I guess
that's why they've got excel.misc <g>.

I have a simple table of data like this:

Date Field1 Field2 Field3
7/14/2003 123 254 75
7/15/2003 201 10 124
7/16/2003 215 258 2
7/17/2003 78 8 89
7/18/2003 125 186 582
7/19/2003 254 45 257
7/20/2003 200 199 89

The table is always the same size, but the data changes as
the days go by.

What I'd like to do is have the table look like a card.
Sort of like a report card or other. My idea is a
rectangle around the table, edges rounded, with the
boxes 'punched out' to allow the fields and numbers to
show. So it would look sort of like someone took a card
and wrote or typed the values in.

Am I making sense? Can someone help me do that?

tod
 
I think I'd live with the borders that are under Format|Cells|border tab, but
this might be ok.

Tools|Customize|
Show the Drawing Toolbar
On the drawing toolbar, click on the AutoShape button
On the next popup, click on the Basic Shapes button
Then let your cursor linger over the icons. (I think you want the one that has
a tooltip of "Rounded Rectangle"

Draw this over the first cell you want.
You can use the alt and shift key for aligning it perfectly.

Then right click on the rounded rectangle and choose "format autoshape"
On the colors and lines tab, make the transparency 100%.
(These rectangles actually float over the cell--not in them.)
Then click on the properties tab.
Click on "move and size with cells"

After you've done this, you might find it difficult to edit the cell underneath
the rectangle. I like to use the arrow keys to get "under" the shapes.

If you like this when you did it by hand, you could have a macro do all the
dirty work for you. Just select the range of cells to get this rounded
rectangle and run this:

Option Explicit
Sub testme()

Dim myRRect As Shape
Dim myCell As Range

For Each myRRect In ActiveSheet.Shapes
If myRRect.Type = msoAutoShape Then
If myRRect.AutoShapeType = msoShapeRoundedRectangle Then
myRRect.Delete
End If
End If
Next myRRect

For Each myCell In Selection.Cells
With myCell
Set myRRect = .Parent.Shapes.AddShape _
(Type:=msoShapeRoundedRectangle, _
Top:=.Top, Width:=.Width, _
Height:=.Height, Left:=.Left)
myRRect.Fill.Transparency = 1
myRRect.Placement = xlMoveAndSize
myRRect.Name = "RRect_" & .Address(0, 0)
End With
Next myCell

End Sub

This does delete existing rounded rectangles. (I thought it made it easier for
testing!). But you can delete that first part that deletes stuff and just use
the part that adds rectangles.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top