Vba to set line chart Yaxis Max, Min, Major Unit

[scottc_00]

I am trying to use vba to set the Yaxis Max, Min and Major Units for a dynamic line chart. The chart changes based on user selections from a data validation cell.
I am using this code

ActiveSheet.ChartObjects("Chart 3").Activate
With ActiveChart.Axis(xlValue)
.TickLabels.AutoScaleFont = False
.MaximumScale = Sheets("Compile").Range("AI25").Value
.MinimumScale = Sheets("Compile").Range("AI24").Value
.MajorUnit = Sheets("Compile").Range("AI26").Value
End With

But when I run it the plot area moves around inside the chart area and the chart area resizes.
After looking around for a solution, I tried “locking” the axis font autoscale like this.
.TickLabels.AutoScaleFont = False

This caused everything to move around but didn’t really fix the problem.
So I tried setting values for the Chart and plot areas like this…
With ChartArea
.Left = 740
.Width = 330
.Top = 140
.Height = 210
End With
With PlotArea
.Left = 0
' .Width = 335
.Top = 5
' .Height = 220
End With

But it doesn’t fix the issue.

All I want to do is set the Yaxis Max, Min and Major unit based on a calculation I am doing on the data. But when I apply it, it seems to change a lot of other settings on the chart. The more things I try to fix it, The more confused I get about what’s helping and what’s hurting. Anyone have any insight on this issue?

Thanks

[protonLeah]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[scottc_00]

I am trying to use vba to set the Yaxis Max, Min and Major Units for a dynamic line chart. The chart changes based on user selections from a data validation cell.
I am using this code

Please Login or Register  to view this content.

But when I run it the plot area moves around inside the chart area and the chart area resizes.
After looking around for a solution, I tried “locking” the axis font autoscale like this.

Please Login or Register  to view this content.

This caused everything to move around but didn’t really fix the problem.
So I tried setting values for the Chart and plot areas like this…

Please Login or Register  to view this content.

But it doesn’t fix the issue.

All I want to do is set the Yaxis Max, Min and Major unit based on a calculation I am doing on the data. But when I apply it, it seems to change a lot of other settings on the chart. The more things I try to fix it, The more confused I get about what’s helping and what’s hurting. Anyone have any insight on this issue?

Thanks

[protonLeah]

You didn't attach a workbook, so I experimented with an old one, adjusting the Y (i.e. value) axis:

Please Login or Register  to view this content.