Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scaling Charts
I am trying to get the mininmum value of my Y axis to link to a cell with in
my worksheet. I want the other axis settings to remain on auto. I want the minimum value to equal sell $S$3 everytime Cell $A$3 changes. Below is what I have so far, but this doesn't work. When I run the code the End Sub is lit up. Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Address = "$A$3" Case Range("$S$3") With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) .MinimumScale = "$S$3" End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scaling Charts
To be sure your code runs every time A3 changes:
If Not Intersect(Target, Range("A3")) Is Nothing Then With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) .MinimumScale = Range("S3").Value End With End If Also, unless you have a control in place to ensure S3 has a valid numeric value, you should probably include some form of error handling. "KatieW" wrote: I am trying to get the mininmum value of my Y axis to link to a cell with in my worksheet. I want the other axis settings to remain on auto. I want the minimum value to equal sell $S$3 everytime Cell $A$3 changes. Below is what I have so far, but this doesn't work. When I run the code the End Sub is lit up. Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Address = "$A$3" Case Range("$S$3") With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) .MinimumScale = "$S$3" End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scaling Charts
Thank you B Lynn B,
I did realize that I was referencing the wrong chart; below is the new code. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A3")) Is Nothing Then With ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) .MinimumScale = Range("S3").Value End With End If I'm getting the compile error on the "End If" I also tried "End Sub" same error. Also, the minimum scale value still isn't changing. Also, thanks for the tip I added some error handling to S3 to ensure a round and positive value. "B Lynn B" wrote: To be sure your code runs every time A3 changes: If Not Intersect(Target, Range("A3")) Is Nothing Then With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) .MinimumScale = Range("S3").Value End With End If Also, unless you have a control in place to ensure S3 has a valid numeric value, you should probably include some form of error handling. "KatieW" wrote: I am trying to get the mininmum value of my Y axis to link to a cell with in my worksheet. I want the other axis settings to remain on auto. I want the minimum value to equal sell $S$3 everytime Cell $A$3 changes. Below is what I have so far, but this doesn't work. When I run the code the End Sub is lit up. Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Address = "$A$3" Case Range("$S$3") With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) .MinimumScale = "$S$3" End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scaling Charts
You need both the End If and the End Sub lines, as in below. That alone may
make your code actually run so the scale value will change on your page. Otherwise, you could look at a couple of things. You might want to check that you have only one chart on the page with the name "Chart 2". It is possible to have duplicate use of the name, which definitely could confuse things. You may also need to specify the second argument for the Axes method, which I went ahead and added below. Beyond that, I know I've heard there are lots of differences between Excel 2007 and earlier versions when it comes to charts. So if you're not running 2007, I suppose that could make the code work for me but not for you. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A3")) Is Nothing Then With ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue, xlPrimary) .MinimumScale = Range("S3").Value End With End If End Sub "KatieW" wrote: Thank you B Lynn B, I did realize that I was referencing the wrong chart; below is the new code. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A3")) Is Nothing Then With ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) .MinimumScale = Range("S3").Value End With End If I'm getting the compile error on the "End If" I also tried "End Sub" same error. Also, the minimum scale value still isn't changing. Also, thanks for the tip I added some error handling to S3 to ensure a round and positive value. "B Lynn B" wrote: To be sure your code runs every time A3 changes: If Not Intersect(Target, Range("A3")) Is Nothing Then With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) .MinimumScale = Range("S3").Value End With End If Also, unless you have a control in place to ensure S3 has a valid numeric value, you should probably include some form of error handling. "KatieW" wrote: I am trying to get the mininmum value of my Y axis to link to a cell with in my worksheet. I want the other axis settings to remain on auto. I want the minimum value to equal sell $S$3 everytime Cell $A$3 changes. Below is what I have so far, but this doesn't work. When I run the code the End Sub is lit up. Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Address = "$A$3" Case Range("$S$3") With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) .MinimumScale = "$S$3" End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pie Charts - Scaling | Charts and Charting in Excel | |||
Auto Scaling Charts | Charts and Charting in Excel | |||
Dynamic Scaling for Charts | Charts and Charting in Excel | |||
Logarithm scaling for charts | Charts and Charting in Excel | |||
Auto Scaling in charts | New Users to Excel |