Change Axes Scale Dynamically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been posting here for the past week about a project I have been
working on. First off I want to thank each of you who assisted, I really
appreciate it.
Well I pretty much have it complete, but it seems when I think that a nother
problem pops ups. I have a workbook with about 15-20 worksheets which are
updated from other workbooks. Also, on this workbook I have a main worksheet
which holds the averages of all the worksheets combined. Some columns are
formatted as number other columns are formatted as percentages. I have a
combo box which is populated with a range and updates a cell with a number.
Now I have a couple range formulas to update a chart dynamically. This all
works fine. However, I want to be able to lock in the scale for the numbers
I would like max 100 min 50. If I choose a percentage column I would like
0%-100%. I created a formula worksheet to assist on some of my ranges. On
this worksheet for one cell I created an or statement depending on which
column was selected from the combo box on the main sheet it would update the
one cell with true or false. On the formula worksheet I created an axis x
with max, min, and major. Depending whether the one cell is true or false it
would populate the cells to the proper numbers. This works fine as well.
Now my problem is trying to connect these numbers for my chart scales. I
looked at a few conversations here and a couple websites, but I still am
unable to update my chart scale using those cell numbers. Can anyone assist
on me on this? Any help would be appreciated.
 
Fysh -

Make two master charts, one for numbers 50 to 100, the other for percentages 0 to
100%. Whichever type of data is chosen, show the appropriate chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
I figured it out. However, I had to place the formulas on my active sheet.
Here is my code I used, I was able to convert some of what I saw on this news
group and your web site at
http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

Private Sub Worksheet_Calculate()
With Me.ChartObjects("Chart 2").Chart.Axes(xlValue, xlPrimary)
.MaximumScale = Me.Range("Z4").Value
.MinimumScale = Me.Range("Z5").Value
.MajorUnit = Me.Range("Z6").Value
End With
End Sub

I am still learning Excel, is there a way to refer to the formulas worksheet
instead of the active sheet? I really don't want to see useless information
on the main sheet.

Thanks for your input.
 
Fysh -

You would have to graduate from worksheet events (on the worksheet's code module) to
workbook events (on the ThisWorkbook code module). It works in the same fashion.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top