ExpExchHelp
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
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
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.
ASKER
englanddg -- any suggestions on the VBA code? Thank you in advance!
EEH
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
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
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Thomas
ASKER
nutsch:
VERY IMPRESSIVE SOLUTION!!! Thousand thanks!
EEH
VERY IMPRESSIVE SOLUTION!!! Thousand thanks!
EEH
ASKER
MOST EXCELLENT SOLUTION!
Glad to help
Thomas
Thomas
ASKER
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
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
ASKER
Thomas:
Never mind the last question... I figured it out.
EEH
Never mind the last question... I figured it out.
EEH