Scaling Charts
in [Programming]
|
From: KatieW on 2 Jun 2010 12:35 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
From: B Lynn B on 2 Jun 2010 12:58 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 > >
From: KatieW on 2 Jun 2010 16:03 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 > > > >
From: B Lynn B on 2 Jun 2010 18:49 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 > > > > > >
|
Pages: 1 Prev: Visual Basic, Office 2007 automation errors Next: Updateing Access DB |