Adding oval shapes based on cell value - Excel VBA

CakzPrimz

Board Regular
Joined
Oct 6, 2017
Messages
55
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
Joined
Mar 10, 2004
Messages
19,617
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!
 
Solution

CakzPrimz

Board Regular
Joined
Oct 6, 2017
Messages
55
Dear Domenic,

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

PROBLEM SOLVED .

Regards,
Prima - Indonesia
 

CakzPrimz

Board Regular
Joined
Oct 6, 2017
Messages
55
Dear Domenic,

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

Thank you
Prima - Indonesia
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,617
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?
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top