Graphs - x axis label range

G

Guest

Guest
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).
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
GOT IT! - Graphs - x axis label range

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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top