Solved

Dynamically grow two circles (in Excel)

Posted on 2014-03-25
12
500 Views
Last Modified: 2014-03-26
In a previous post, I received excellent feedback on some calculations for "circles".   See URL below for additional details:
http://www.experts-exchange.com/Other/Math_Science/Q_28396172.html#a39953218

As a follow-on question, I now would like to dynamically "resize" the sizes of 2 circles.  

Please see attached XLS with an example.   For this example XLS, I'm only using two sizes for the inner and outer circle (see drop-down values in cell B1 and B2.

Also, as indicated in notes section of this XLS, I must ensure that the inner circle will always be centered within the outer circle.  

Can this be done in Excel?

EEH
DynamicCircles.xlsx
0
Comment
Question by:ExpExchHelp
  • 7
  • 4
12 Comments
 
LVL 3

Expert Comment

by:englanddg
ID: 39953529
It can.  You will need to use a VBA macro that fires off when the cell is updated to modify the values of the circles.
0
 

Author Comment

by:ExpExchHelp
ID: 39953542
englanddg -- any suggestions on the VBA code?   Thank you in advance!

EEH
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39954014
You can use something like this, adjusting from AlignTwoOnOne shpCircle1, shpCircle2 to AlignTwoOnOne shpCircle2, shpCircle1 whether you want to align on one shape or the other.

Thomas

Sub AlignCenters()
Dim shpCircle1 As Shape, shpCircle2 As Shape

Set shpCircle1 = ActiveSheet.Shapes("Oval 3")
Set shpCircle2 = ActiveSheet.Shapes("Oval 4")

AlignTwoOnOne shpCircle2, shpCircle1

'or use
'AlignTwoOnOne shpCircle1, shpCircle2

End Sub

Private Sub AlignTwoOnOne(shp1 As Shape, shp2 As Shape)
'center based on the outside shape being square

shp2.Left = shp1.Left - (shp2.Width - shp1.Width) / 2
shp2.Top = shp1.Top - (shp2.Height - shp1.Height) / 2



End Sub

Open in new window

0
 

Author Comment

by:ExpExchHelp
ID: 39954221
nutsch:

Thanks for taking a stab at the VBA code.   I've plugging it into the worksheet but I'm not sure how to link cell values in B1 and B2 so that a) sizes will increase or decrease and b) green circle is aligned with red circle.

Any thoughts on how to get this accomplished?   See attached XLS for more details.

EEH
DynamicCirclesWithVBA.xlsm
0
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 39954237
Use this code in your worksheet module:

Thomas

Private Sub Worksheet_Change(ByVal Target As Range)

'turn off updates to speed up code execution
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

If Not Intersect(Target, Range("B1:B2")) Is Nothing Then

    ActiveSheet.Shapes("Oval 1").Height = [b1] * 100
    ActiveSheet.Shapes("Oval 1").Width = [b1] * 100
    ActiveSheet.Shapes("Oval 2").Height = [b2] * 100
    ActiveSheet.Shapes("Oval 2").Width = [b2] * 100
    
    If Not Intersect(Target, Range("B1")) Is Nothing Then
        AlignTwoOnOne ActiveSheet.Shapes("Oval 2"), ActiveSheet.Shapes("Oval 1")
    Else
        AlignTwoOnOne ActiveSheet.Shapes("Oval 1"), ActiveSheet.Shapes("Oval 2")
    End If
    
End If

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With


End Sub

Private Sub AlignTwoOnOne(shp1 As Shape, shp2 As Shape)
'center based on the outside shape being square

shp2.Left = shp1.Left - (shp2.Width - shp1.Width) / 2
shp2.Top = shp1.Top - (shp2.Height - shp1.Height) / 2

End Sub

Open in new window

0
 

Author Comment

by:ExpExchHelp
ID: 39954809
nutsch:

I've added the code to the worksheet.   Names are "Oval 1" and "Oval 2".

Still, at this time, the shape sizes don't change when modifying the values in cells B1:B2.

What am I missing?    Attached is the latest version.
EEH
DynamicCirclesWithVBA-v02.xlsm
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 39

Expert Comment

by:nutsch
ID: 39954817
You've put the code in the workbook module rather than the worksheet module. Move it to the Dynamic Circles sheet code and it should work just fine.

Thomas
0
 

Author Comment

by:ExpExchHelp
ID: 39954966
nutsch:

VERY IMPRESSIVE SOLUTION!!!   Thousand thanks!

EEH
0
 

Author Closing Comment

by:ExpExchHelp
ID: 39954967
MOST EXCELLENT SOLUTION!
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39954994
Glad to help

Thomas
0
 

Author Comment

by:ExpExchHelp
ID: 39956002
nutsch:

Quick follow-up question....

I'm integrating the solution (growing circles) into an existing Excel file/project.   Here are the steps that I performed (as part of integration) but something is missing (thus circles don't grow in project file).

1. Inserted two ovals (ensured they're called "Oval 1" and "Oval 2")
2. Copied VBA code into same worksheet
3. Save file as macro-enabled (given other requirements in project) XLS (i.e., XLSM).

When changing cell values, however, the circles' sizes don't changes.   Is there another "connecting" piece that must be integrated?

EEH
0
 

Author Comment

by:ExpExchHelp
ID: 39956026
Thomas:

Never mind the last question... I figured it out.

EEH
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now