Graphs - x axis label range

I have a simple graph where I plot certain water flows (y axis) against time (x axis) in units of months. The range that defines the x axis min. and max. limits changes very frequently, necesitating redefining the x axis label range manually. Is there a way of linking the upper and lower limits of the x range (or y range Min. & Max. for that matter) with a formula in the spreadsheet? I've tried using the INDIRECT function, but that doesn't work. Defining the Min. Max. and ranges to be graphed appears to only be do-able by setting them manually (or using the pointing method) through the Chart's source data or scale settings (or by defining a macro to do that for you).

[Damon Ostrander]

Here is a simple event macro that will set a chart's scale max and min whenever the value in cells named MaxXaxis and MinXaxis are edited. It assumes the chart is embedded on the sheet and that it is the first chart on the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("MinXaxis").Address Then
ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = Target.Value
ElseIf Target.Address = Range("MaxXaxis").Address Then
ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = Target.Value
End If
End Sub

This code must be placed in the worksheet's event code module. To do this, right-click on the worksheet tab, select View Code, and paste the code into the VBE code pane that appears.

Thanks Damon!!!

[wguidry]

Damon,

What if my chart and the max & min I want to use are on different sheets? I would like to use two calculated cells on "Sheet1" to update the max and min "Y" scale on an embedded chart on "Sheet2".

Cheers.

[wguidry]

Damon,

P.S. - I want to put the code in the click event of a button on the sheet with the data so that it doesn't run every time I activate the sheet.

[wguidry]

Here's what I've tried so far (obviously, this doesn't work):

Private Sub cmdScale_Click()
Dim rngHigh As Range
Dim rngLo As Range
Set rngHigh = Worksheets("Sheet1").Range("E1")
Set rngLo = Worksheets("Sheet1").Range("F1")
Worksheets("Sheet2").ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = rngLo
Worksheets("Sheet2").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = rngHigh
End Sub

[Damon Ostrander]

Hi again wguidry,

I don't see anything wrong with your code, although I do not see the necessity for the range object variables. What kind of an error did you get?

Perhaps you should give this a try:

Private Sub cmdScale_Click()
Dim dblHigh As Double
Dim dblLo As Double
dblHigh = Worksheets("Sheet1").Range("E1").Value
dblLo = Worksheets("Sheet1").Range("F1").Value
Worksheets("Sheet2").ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = dblLo
Worksheets("Sheet2").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = dblHigh
End Sub

Damon

[wguidry]

I get the error - "Run-time error '1004': Unable to set the MinimumScale property of the Axis class.

I bolded the line below where the debugger stops.

Private Sub cmdScale_Click()
Dim rngHigh As Double
Dim rngLo As Double
rngHigh = Worksheets("Weekly Calcs").Range("E1")
rngLo = Worksheets("Weekly Calcs").Range("F1")
Worksheets("Weekly Indicators").ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = rngLo
Worksheets("Weekly Indicators").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = rngHigh
End Sub

[wguidry]

Damon,

When I step into the subroutine in the debugger, I get the following message at the bold line below: "Run-time error '1004': Application-defined or object-defined error."

Both of the variables populate with the correct values (I viewed them in the Watch window). Does it need to know which axis the values or for?


Private Sub cmdScale_Click()
Dim rngHigh As Double
Dim rngLo As Double
rngHigh = Worksheets("Weekly Calcs").Range("E1").Value
rngLo = Worksheets("Weekly Calcs").Range("F1").Value
Worksheets("Weekly Indicators").ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = rngLo
Worksheets("Weekly Indicators").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = rngHigh
End Sub

[wguidry]

Damon,

It was the (xlCategory). It wanted (xlValue). aaaaaahhhhhhh!!!

OK, I can sleep now.

Hey, thanks for all your help. You did all the heavy lifting on this one.

Private Sub cmdScale_Click()
Dim rngHigh As Double
Dim rngLo As Double
rngHigh = Worksheets("Weekly Calcs").Range("E1").Value
rngLo = Worksheets("Weekly Calcs").Range("F1").Value
Worksheets("Weekly Indicators").ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = rngLo
Worksheets("Weekly Indicators").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = rngHigh
End Sub