Solved

Dynamically grow two circles (in Excel)

Posted on 2014-03-25
12
520 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article seeks to propel the full implementation of geothermal power plants in Mexico as a renewable energy source.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

808 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