Excel VBA shape arrat

[mbri0029]

hi guys,

i need to be able to create an array of circles. for example, if cell A1 has the value 5 in it, and cell A2 has the value 600, then i need to be able to fit 5 circles in the width of 600... I hope i explained myself properly.

Thanks

 

[MickG]

Try this:-
NB:- You can get the basics for this , from the Macro recorder

[COLOR="Navy"]Sub[/COLOR] MG26Jul27
[COLOR="Navy"]Dim[/COLOR] Shp [COLOR="Navy"]As[/COLOR] Shape, Shp1 [COLOR="Navy"]As[/COLOR] Shape, Dm [COLOR="Navy"]As[/COLOR] Double, Shp2 [COLOR="Navy"]As[/COLOR] Shape, Shp3 [COLOR="Navy"]As[/COLOR] Shape, Shp4 [COLOR="Navy"]As[/COLOR] Shape, Shp5 [COLOR="Navy"]As[/COLOR] Shape
Dm = [a2] / [a1]
[COLOR="Navy"]Set[/COLOR] Shp = ActiveSheet.Shapes.AddShape(msoShapeOval, 20, 20, [a2], [a2])
[COLOR="Navy"]Set[/COLOR] Shp1 = ActiveSheet.Shapes.AddShape(msoShapeOval, 20, 20 + [a2] / 2 - Dm / 2, Dm, Dm)
Shp1.Fill.ForeColor.SchemeColor = 2
[COLOR="Navy"]Set[/COLOR] Shp2 = ActiveSheet.Shapes.AddShape(msoShapeOval, 20 + Dm, 20 + [a2] / 2 - Dm / 2, Dm, Dm)
Shp2.Fill.ForeColor.SchemeColor = 3
[COLOR="Navy"]Set[/COLOR] Shp3 = ActiveSheet.Shapes.AddShape(msoShapeOval, 20 + Dm * 2, 20 + [a2] / 2 - Dm / 2, Dm, Dm)
Shp3.Fill.ForeColor.SchemeColor = 4
[COLOR="Navy"]Set[/COLOR] Shp4 = ActiveSheet.Shapes.AddShape(msoShapeOval, 20 + Dm * 3, 20 + [a2] / 2 - Dm / 2, Dm, Dm)
Shp4.Fill.ForeColor.SchemeColor = 5
[COLOR="Navy"]Set[/COLOR] Shp5 = ActiveSheet.Shapes.AddShape(msoShapeOval, 20 + Dm * 4, 20 + [a2] / 2 - Dm / 2, Dm, Dm)
Shp5.Fill.ForeColor.SchemeColor = 6

Regards Mick