Charts - auto format the scale (x-axis) by cell ref?

[nancyo]

I tried a search, but cannot get this to work today.

Is there a way to change the format of an axis in a chart by referencing a specific cell?

By this, can the MAX or MIN on the scale be referenced to a specific cell?

 

[Mudface]

Only through code I think. If you're willing to use code, the following would work OK for a chart embedded as an object on a worksheet, with the Min value in cell A1 and the Max in cell A2. Paste it into a standard module, select the chart and then run it:-

Public Sub ChartScale()

With ActiveChart.Axes(xlValue)
    .MinimumScale = Range("A1")
    .MaximumScale = Range("A2")
End With

End Sub

 

[btadams]

you could automate this by putting some code like this in the Worksheet_Change event. In this example the x-axis of a chart named "Chart 1" will be scaled to whatever the user enters into cells G2 and H2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("G2") Or Target = Range("H2") Then
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.Axes(xlCategory)
If Range("G2").Value <> "" Then .MinimumScale = Range("G2").Value
If Range("H2").Value <> "" Then .MaximumScale = Range("H2").Value
End With
End If
End Sub

if you don't know what your chart name is, try replacing the chart name with an index number, ActiveSheet.ChartObjects(1).Activate

 

[nancyo]

I will try the more complicated coding in a sec - i did try the first response, and the Y-axis changed...not the X-axis.

Any idea why?

 

[Mark W.]

I tried a search, but cannot get this to work today.

Is there a way to change the format of an axis in a chart by referencing a specific cell?

By this, can the MAX or MIN on the scale be referenced to a specific cell?

Why wouldn't you simply control the MAX and MIN by manipulating the data that's plotted?

 

[nancyo]

Because I am making this a generic spreadsheet, which I am going to have protected. Others will be accessing the file, and may want to change the scales.

I want to be able to enter values into cells and change the X-axis scale or Y-axis scale.

I don't want anyone actually changing the graph, as there are many data series on the graph, and I don't want it messed up.

 

[Mudface]

To change the x-axis, try changing the first line to-

With ActiveChart.Axes(xlCategory)

 

[Mark W.]

Define a named range, Sheet1!X_Axis, that refers to the formula shown below, and use that defined name in your chart's SERIES function. Also, create a named range, Sheet1!Y_Axis, that refers to the formula...

=OFFSET(Sheet1!X_Axis,,1)

...and supplies the appropriate Y-values to your chart's SERIES function.

Book1
	A	B	C	D	E	F	G	H	I	J
1	X	Y		Min	2.8
2	1	17		Max	7.8
3	2	12
4	3	7		3
5	4	22
6	5	8
7	6	6
8	7	8
9	8	6
10	9	9
11	10	20
12
Sheet1

 

[nancyo]

Mark - WAY over my head.

I understand some basic VBA. Tried the more complicated code (to me at least), it keps getting hung up at .Maximum...or Minimum depending on wether I put a value into one cell or the other.

When I enter data into G2, get a debug for H2 and vice versa...any idea why?

 

[btadams]

nancyo,

what kind of chart do you have?