Link to home
Start Free TrialLog in
Avatar of ExpExchHelp
ExpExchHelpFlag for United States of America

asked on

Dynamically grow two circles (in Excel)

In a previous post, I received excellent feedback on some calculations for "circles".   See URL below for additional details:
https://www.experts-exchange.com/questions/28396172/Calculating-length-distance-of-line-within-a-circle.html?anchorAnswerId=39953218#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
Avatar of englanddg
englanddg

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.
Avatar of ExpExchHelp

ASKER

englanddg -- any suggestions on the VBA code?   Thank you in advance!

EEH
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

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
ASKER CERTIFIED SOLUTION
Avatar of nutsch
nutsch
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
nutsch:

VERY IMPRESSIVE SOLUTION!!!   Thousand thanks!

EEH
MOST EXCELLENT SOLUTION!
Glad to help

Thomas
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
Thomas:

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

EEH