Creating shapes and position them

[Kalle1202]

Hey everybody,

Iam just starting to learn VBA programming in Excel.

All I want is to create a big circle and some small circles in excel. The big circle has to be in a certain position.

My excel sheet looks like this:

Capture.JPG
My code looks like this:

Private Sub CommandButton1_Click()

Dim diam
diam = Range("K8").Value * 10

ActiveSheet.Shapes.AddShape msoShapeOval, 50, 50, diam, diam

End Sub


The code creates the big circle but it is in the wrong position as you can see in the picture. I would like to have it somewhere close to I25. Then by clicking on the command button I also want to create 7 more small circles with a diameter of 5.922. (please see also the picture cell K17 and K25). The position of the small circles is not important.

Thank you guys so much

[Bob Phillips]

Try this

Dim nTop As Double
Dim nLeft As Double
Dim diam
 
    diam = Range("K8").Value * 10
    
    With Range("I25")
    
        nTop = .Top
        nLeft = .Left
    End With
    
    ActiveSheet.Shapes.AddShape msoShapeOval, nLeft, nTop, diam, diam

[p45cal]

…with a bit added for the smaller circles:

Dim nTop As Double
Dim nLeft As Double
Dim diam, i As Long
 
diam = Range("K8").Value * 10
With Range("I25")
  nTop = .Top
  nLeft = .Left
End With
ActiveSheet.Shapes.AddShape msoShapeOval, nLeft, nTop, diam, diam
diam = Range("K25").Value * 10
For i = 1 To Range("K17").Value
  ActiveSheet.Shapes.AddShape msoShapeOval, nLeft + i * 3, nTop + i * 3, diam, diam
Next i

[Bob Phillips]

…with a bit added for the smaller circles

I noticed I missed that bit afterwards so corrected the code, I guessed where the OP wanted them as you did but I used a multiplier of 10 not 3, but was holding it in reserve for when the OP pointed out my omission ... and I could say 'here is one I prepared earlier'