Autoscale chart axes based on user inputs

[glad_ir]

Hello,

Can anybody help me with this one please?

Is it possible to auto scale chart axes based on cell values?

I have 4 cells of interest - A1 & A2 describe the x axis range of interest and B1 & B2 the y axis range of interest. Ideally I would like my chart to scale the x axis to between 90% of A1 value to 110% of A2 value and the y axis similarly from 90% of B1 value to 110% B2 value.

So for example if cell A1 = 200, A2 = 1000, B1 = 0.5, B2 = 1.5 I'd like my chart x-axis to run from 180-1100 and my y-axis from 0.45-1.65.

Is this possible? Any help is much appreciated!

Thanks!
Iain

 

[kevin9999]

Hi Iain,

You're really talking about 2 distinct methods here. To scale the Y axis, you use code as per below. You said "auto scale" so presumably you want it to run automatically if a change happens in cells B1 or B2. I can help with that but try the standard macro below first to see if it does what you want:

Sub ScaleMe()
    Sheets(1).ChartObjects(1).Activate
    With ActiveChart.Axes(xlValue, xlPrimary)
        .MaximumScale = Sheets(1).Range("B2").Value2 * 1.1
        .MinimumScale = Sheets(1).Range("B1").Value2 * 0.9
    End With
End Sub

As far as scaling the X axis, this is really achieved via worksheet formulas. There's probably a more elegant way than that below, but but seems to work.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	100	0.5
2	1000	1.5
3
4
5
6
7
8
9
10
11
12
13
14
15
16			Chart Data Used
17			period	100	200	500	750	1000
18			value	0.25	0.5	0.75	1	1.25
19
20			Total Chart Data
21			period	100	200	500	750	1000	1200	1500	2000
22			value	0.25	0.5	0.75	1	1.25	1.5	1.75	2
23
Sheet1

Cell Formulas
Range	Formula
D17	D17	=A1
E17:K17	E17	=IFERROR(IF(OFFSET(INDEX($D$21:$K$21,MATCH(D17,$D$21:$K$21,0)),0,1,1,1)<=$A$2,OFFSET(INDEX($D$21:$K$21,MATCH(D17,$D$21:$K$21,0)),0,1,1,1),""),"")
D18:K18	D18	=IFERROR(HLOOKUP(D17,D21:K22,2,FALSE),"")
E22:K22	E22	=D22+0.25

 

[glad_ir]

This is perfect!! Thank you for your help.

Iain

 

[kevin9999]

This is perfect!! Thank you for your help.

Iain

Happy to help Iain and thanks for the feedback