yet another x axis question
David Robbins
on the x axis and a couple of values plotted on the y. i need to zoom in
and look at one day at a time. by changing the min and max values on the x
axis scale i can do this very nicely. unfortunately i need to do this a lot
so i was going to make a couple buttons and have them set the axis start and
end points for me. i have read some of the other problems on this group
relating to this but none of them seem to answer the question. it looked
easy when i started, just get the axis object and set it's properties, but
when i try this it doesn't seem to exist. is this called something else for
a scatter chart? using excel 2002 here.
Jon Peltier
I made up a little example. There are two charts: Chart 2, a line chart with
a time scale category axis, and Chart 3, an XY scatter chart with a value
category axis. (So I goofed up Chart 1, big deal) I put my desired X minimum
in cell B3 and X maximum in cell B4. This code is in the worksheet code
module (right click on the sheet tab, and select view code).
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Me.Range("B3:B4")) Is Nothing Then
'' Is one of the scale parameters changed?
ChartTimeAxisScale
End If
End Sub
Sub ChartTimeAxisScale()
With Me.ChartObjects("Chart 2").Chart.Axes(xlCategory)
'' Time Scale Axis (Line Chart)
.MinimumScale = ActiveSheet.Range("B3").Text
.MaximumScale = ActiveSheet.Range("B4").Text
End With
With Me.ChartObjects("Chart 3").Chart.Axes(xlCategory)
'' Value Axis (XY Scatter Chart)
.MinimumScale = ActiveSheet.Range("B3").Value
.MaximumScale = ActiveSheet.Range("B4").Value
End With
End Sub
I thought it was going to matter whether I used .Text or .Value for the
respective chart type because of what the macro recorder recorded, but then I
experimented with them, and it doesn't matter at all. So this procedure works
just as well:
Sub ChartTimeAxisScale()
Dim oChart As ChartObject
For Each oChart In Me.ChartObjects
With oChart.Chart.Axes(xlCategory)
.MinimumScale = ActiveSheet.Range("B3").Value
.MaximumScale = ActiveSheet.Range("B4").Value
End With
Next
End Sub
- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
In article <#G9ap3QdCHA.2060@tkmsftngp09>, drob...@eprisolutions.com says...
David Robbins
right objects was:
Private Sub CommandButton2_Click()
Sheets("calcs").Select
StartDate = ActiveSheet.Range("RateStartDate")
EndDate = ActiveSheet.Range("RateEndDate")
Sheets("rate").Select
Application.Charts(1).Axes(xlCategory).MinimumScale = StartDate
Application.Charts(1).Axes(xlCategory).MaximumScale = EndDate
Sheets("calcs").Select
End Sub
rude and crude, but it got the job done. the confusion i was having was
about where the charts seemed to be. i was looking in ActiveSheet but
couldn't seem to get the right reference. could it be because this
particular chart was a whole worksheet chart and not just a small chart on
another sheet??
"Jon Peltier" <jonpe...@yahoo.com> wrote in message
news:etUr0zUdCHA.1864@tkmsftngp11...
Jon Peltier
Apparently you were working with a chart sheet, not a chart embedded in a
worksheet. To remove yourself from the requirements to have an activesheet
and activechart, you can use the sheet tab names:
Private Sub CommandButton2_Click()
StartDate = Worksheets("calcs").Range("RateStartDate")
EndDate = Worksheets("calcs").Range("RateEndDate")
Charts("MyChartTabName").Axes(xlCategory).MinimumScale = StartDate
Charts("MyChartTabName").Axes(xlCategory).MaximumScale = EndDate
End Sub
I would then use a command bar button, instead of a button on the sheet.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
In article <#$k1#tddCHA.1332@tkmsftngp08>, drob...@eprisolutions.com says...
David Robbins
hopefully i'll remember!
"Jon Peltier" <jonpe...@yahoo.com> wrote in message
news:#k20YofdCHA.2408@tkmsftngp11...