4 replies to this topic

[GeoffS]

I am trying to place a drawing object (round circle) onto a picture using a macro (don't ask why!!!). I have enabled all macros, and selected to save the mcro in the open workbook. I record the process, and stop recording, but when I run the macro nothing happens. When I look at the macro in VBA this is what I see: Sub Macro1() ' ' Macro1 Macro ' ' End Sub What am I missing ?

[kalasch]

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 :


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

[GeoffS]

Thanks, elnikoff, for taking the time to consider my problem I will try it and see where I get to. Regards, Geoff

[kalasch]

You're welcome, Geoffs... Well, at work, they call me 'macro man' on the rythm of the Village people song... So, in case of further need, can you give your software configuration ? Which version of Office do you use ?

[GeoffS]

Sorry, elnikoff (Macro Man), meant to add that last time. I use Office 2007. Regards Geoff