Adding oval shapes based on cell value - Excel VBA

[CakzPrimz]

Dear Folks,

I have a number in cell G4 for example 8
How to add oval shapes, based on G4, if cell value of G4 is 8, then it will add 8 oval shapes.

Thank you
Prima - Indonesia

 

[Domenic]

The following solution uses the worksheet change event to automatically add the appropriate number of oval shapes, whenever the value in G4 is changed, and the value is a number greater than 0. When the code is executed, it does the following...

1) Deletes any and all existing oval shapes.

2) Places the newly created oval shapes in Column I, starting at I4.

3) Sets their width and height at their default value, which is 72 points, respectively.

Here's the code, which needs to be placed in the code module for the worksheet (right-click the sheet tab, and select View Code). Note that you can make changes as desired.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address <> "$G$4" Then Exit Sub
    
    Me.Ovals.Delete
    
    Dim ovalShape As Oval
    Dim leftPos As Single
    Dim topPos As Single
    Dim gap As Long
    Dim i As Long
    
    leftPos = Target.Offset(, 2).Left
    topPos = Target.Top

    gap = 20 'change the gap between shapes as desired
    
    If Len(Target) > 0 Then
        If IsNumeric(Target) Then
            For i = 1 To Target.Value
                Set ovalShape = Me.Ovals.Add(Left:=leftPos, Top:=topPos, Width:=72, Height:=72)
                topPos = topPos + ovalShape.Height + gap
            Next i
        End If
    End If
    
End Sub

Hope this helps!

 

[CakzPrimz]

Dear Domenic,

Thanks for your help, your magic code is working !
Thanks so much, really appreciate it.

PROBLEM SOLVED .

Regards,
Prima - Indonesia

 

[CakzPrimz]

Dear Domenic,

If I want to add gap to the right, how is the code?

Thank you
Prima - Indonesia

 

[Domenic]

Since the oval shapes are placed one below each other, your question is confusing?

Do you mean that you want the oval shapes placed in another column?

If not, can you please clarify?