PDA

View Full Version : Creating shapes and position them



Kalle1202
03-27-2018, 09:55 AM
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:

21935
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
03-27-2018, 04:27 PM
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
03-28-2018, 07:14 AM
…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
03-28-2018, 09:35 AM
…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' :rotlaugh: