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

Here is a simple example:

The worksheet module contains the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler
    If Not Intersect(Range("D2"), Target) Is Nothing Then
        If Range("D2").Value <> "" Then
            Me.ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = Range("D2").Value
        End If
    End If
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
End Sub

When you change the value of cell D2, the x-axis will automatically be adjusted.

To activate the worksheet module, right-click the sheet tab and select View Code.

Don't forget to save the workbook as a macro-enabled workbook (*.xlsm) and to allow macros.

Answer 3

It looks good, but I can't get it to run automatically. My reference cell has the formula =ROUNDUP(MAX(D38:D39),-2) which will update as other cells update, but the macro only seems to run when I manually type in a new value in the cell as opposed to updating the formula by changes elsewhere. Best I can get is to copy the same formula and paste it a second time and then it updates. I can put in a macro to do that, but it seems I would be better just running your macro from a button with a different type of trigger mechanism. Is there a way to trigger your Worksheet_Change when the cell updates automatically?

Answer 4

Change the code to

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler
    If Not Intersect(Range("D38:D39"), Target) Is Nothing Then
        Me.ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = Range("D2").Value
    End If
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
End Sub

Answer 5

Same issue. If I change the value by changing the data feeding the formula in D2 (D40 in my case), nothing happens. If I copy the D2 formula and paste it back in to the same cell, or simply type a new value, the chart changes to match. The trigger seems to be actually changing the cell contents instead of the results of the formula.