Solved

Dynamically grow two circles (in Excel)

Posted on 2014-03-25
12
507 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article provides a brief introduction to tissue engineering, the process by which organs can be grown artificially. It covers the problems with organ transplants, the tissue engineering process, and the current successes and problems of the tec…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

919 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

20 Experts available now in Live!

Get 1:1 Help Now