# Adding oval shapes based on cell value - Excel VBA

#### CakzPrimz

##### Board Regular
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

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### Domenic

##### MrExcel MVP
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.

VBA Code:
``````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

##### Board Regular
Dear Domenic,

Thanks so much, really appreciate it.

PROBLEM SOLVED .

Regards,
Prima - Indonesia

#### CakzPrimz

##### Board Regular
Dear Domenic,

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

Thank you
Prima - Indonesia

#### Domenic

##### MrExcel MVP
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?

Replies
9
Views
99
Replies
5
Views
97
Replies
3
Views
185
Replies
3
Views
74
Replies
0
Views
300

1,132,685
Messages
5,654,746
Members
418,149
Latest member
amamiche67

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back