Hi GeoffS,
I believe that this is because the macro recording works at worksheet level. Shapes such ovals, lines, and so on, that you can draw with your mouse, are working on another level of the worksheet (the drawing layer) . I'm not an expert of inner choices of Microsoft about drawing shapes, but this can explain why the recording does not work.
However, what you want is not impossible, but you will have to put your hands in the code or your macro. What you want to do can be achieved by using Shapes in your code.
As you did not explicitely give informations about your Excel version, i have to put some bemol about the following code : maybe it won't work. But, you will at least have some direction to investigate…
This code apply to Excel 2007, but Shapes are present in Excel many versions before.
Public Sub test()
Dim wks As Worksheet
Dim shape1 As Shape
Dim shape2 As Shape
Set wks = ActiveSheet
Set shape1 = wks.Shapes.AddShape(msoShapeOval, 100, 100, 50, 50)
Set shape2 = wks.Shapes.AddShape(msoShapeOval, 200, 100, 100, 50)
With shape1
.Line.ForeColor.RGB = RGB(200, 20, 20)
.Fill.ForeColor.RGB = RGB(0, 200, 200)
End With
With shape2.Line
.DashStyle = msoLineDashDotDot
.ForeColor.RGB = RGB(50, 0, 128)
End With
End Sub
This give the following :
[external image: Posted Image]
You'll have to play with all the shapes properties, until you find some way things works for you.
More information about working with shapes can be found on the MSDN site :
Version Excel 2003 :
http://msdn.microsoft.com/en-us/library/aa…office.11).aspx
I hope this will help you
I just wish you to have fun with that
Do not hesitate to come back to me
