Set minimum value on chart axis using VBA

Question

Can you help me with this? I want to generate a minimum axis value for my chart based on a cell reference.

Here is my code.

Private Sub Worksheet_calculate() With ActiveChart.Axes(xlValue) .MinimumScale = Worksheets("Chart Data").Range("E111").Value End With End Sub

What issue are you actually having - is it not working, do you get an error...? — Alex P
– Alex P, Commented Jan 30, 2016 at 11:12
Hello Alex. Thank you for your response. I am having an error of "Run-time error '91': Object variable or With block variable not set" What I actually have is 2 sheets, 1: is the chart data which I put all the data for the chart, 2: is where the chart is. — Jandhell Colipano
– Jandhell Colipano, Commented Feb 2, 2016 at 1:33
You don't identify the ActiveChart. You need to write: With Worksheets("Sheet2").ActiveChart.Axes(xlValue). That worked for me. — Alex P
– Alex P, Commented Feb 2, 2016 at 9:35
Private Sub Worksheet_calculate() With Worksheets("Chart").ActiveChart.Axes(xlValue) .MinimumScale = Worksheets("Chart Data").Range("E111").Value End With End Sub Is this how it is? I got an error: "Run-time eror 438: Object doesn't support this property or method." — Jandhell Colipano
– Jandhell Colipano, Commented Feb 4, 2016 at 5:32
How is E111 getting calculated? The issue is with ActiveChart. If you are in Chart Data when the calculation happens then you get an error as the chart is not active. Instead, try referencing the chart directly by name... — Alex P
– Alex P, Commented Feb 4, 2016 at 8:40

Alex P · Accepted Answer · 2016-02-04 14:23:06Z

I'd do it this way:

Private Sub Worksheet_calculate() Dim cht As Chart Set cht = Worksheets("Chart").ChartObjects("Chart 1").Chart cht.Axes(xlValue).MinimumScale = Worksheets("Chart Data").Range("E111").Value End Sub

Set up a reference to your chart object so you don't need ActiveChart
You may need to update ChartObjects("Chart 1") with the name of your chart
You can also index it so if you only have one chart on the page you can use ChartObjects(1)

Hello. On the cell "Chart Data" E111, I get the minimum date and pass the value to the minimum axes using the general format value. I try using your code with ChartObjects and its good it throws no error but the minimum axis is not updating. I try to check it manually and it says 0.0
Thank you Alex. I got it now. I used a button to trigger run the vba. Thanks a lot.

Collectives™ on Stack Overflow

Set minimum value on chart axis using VBA

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related