Format Axis Maximum by Cell Value

Question

I have a an X Y line chart where the lines are the comparison values, and I plot a few data points. I want to format the x axis to variable maximum value that is lower than the maximum in the data range. Some of the data shows comparison values so it is ok if it is cut off. How do I get the maximum x axis value to be based on a cell value?

Answer 1

You need to change Range("D38:D39") in the code to the cell or cells that you enter manually and that determine the outcome of the formula in D2.

Alternatively, use this code, but it has a higher overhead:

Private Sub Worksheet_Calculate()
    On Error GoTo ErrHandler
    Me.ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = Range("D2").Value
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
End Sub

Answer 2

That one works for me. Thank you!