Formatting axes based on cell values

[doublej41]

I am trying to set the maximum value for the x-axis of my chart based on the value I enter into a cell.

I am using the following VBA code under a worksheet change event:

Option Explicit
x1Category As Range


Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address
    Case "$C$1"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Category) _
            .MaximumScale = Target.Value
    Case Else
End Select

End Sub

When I change the value in cell C1, I get a Run-time error 13 and the message 'Type mismatch'. Can anyone shed some light on what may be causing this error?

I am trying to do this with a XY scatter graph.

Thanks.

 

[doublej41]

Thanks for this, two things though:

1) The X axis maximum will not be A1, I wanted to use A1 for the change as I know this cell will always be updated and will therefore always execute the code. Does that still make this possible?

2) I get the error "Object doesn't support this property or method" for the line of code below.

If Me.ChartObjects("Chart 1").Axes(xlCategory).MaximumScale <> Me.Range("A1").Value Then

I have changed the code to include "Chart 1" for the the ChartObjects, but get the same error with or without that change.

 

[Jon Peltier]

Okay, A1 is the changing cell, A2 has the cell with the calculated axis maximum.

And I left out an important keyword (D'oh!).

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("A1") Is Nothing Then

        If Me.ChartObjects(1).Chart.Axes(xlCategory).MaximumScale <> Me.Range("A2").Value Then

            Me.ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = Me.Range("A2").Value

        End If

    End If

End Sub

 

[doublej41]

That works like a charm.

One thing I have noticed though. A2 contains a formula which references a cell on another sheet, lets say Sheet2!A1. If I update Sheet2!A1 then paste the data into Sheet1!A1 it will work OK, however if I do the opposite, then the value in Sheet1!A2 changes, but the X axis value does not change.

Can we add some code so that either, a) the code will execute whenever Sheet1!A1 or Sheet2!A1 is changed, so that it doesn't matter which order the data is input, or b) prevent the user from pasting data to Sheet1!A1 whilst Sheet2!A1 is blank, with a message instructing them to enter a value into Sheet2!A2? Preferably the first.

Does that make sense? There are lots of Sheets! and A's in there.

 

[Jon Peltier]

Keep Sheet1's code intact. Put this into Sheet2's code module (right click the sheet tab, etc., as above:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("A1") Is Nothing Then

        If Sheets("Sheet1").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale <> Sheets("Sheet1").Range("A2").Value Then

            Sheets("Sheet1").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = Sheets("Sheet1").Range("A2").Value

        End If

    End If

End Sub

 

[doublej41]

Is it possible to make the code on Sheet2 run only when a particular cell is changed?

There are a few cells on Sheet 2 that the user populates and the workbook flickers each time a cell is changed and it executes the code.

 

[n0gel]

I use this all the time where H19 contains a formula. This is for a chart with 2 scatter charts, a line chart and a stacked area chart in one. (which I learned from Jon)


x = Range("$H$19")

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).MaximumScale = x

 

[Jon Peltier]

This line:

If Not Intersect(Target, Me.Range("A1")) Is Nothing Then

(note the missing parenthesis that I inserted here)

bypasses the meat of the procedure if any cell besides A1 is changed.

 

[doublej41]

This line:

If Not Intersect(Target, Me.Range("A1")) Is Nothing Then

(note the missing parenthesis that I inserted here)

bypasses the meat of the procedure if any cell besides A1 is changed.

OK, I thought as much. However, when I change A1 to D31 (which is the cell that changes on my sheet) and update the sheet numbers I get the error "the specified dimension is not valid for the current chart type" when I change cell D31 on Sheet2.

And if I go to Sheet2 it tells me a formula in the sheet contains one or more invalid references.

 

[Jon Peltier]

The formula's in sheet 1?

What does changing D31 in sheet 2 do to the formula in Sheet 1 that calculates the axis max?

 

[doublej41]

This is the code giving me the error:

Keep Sheet1's code intact. Put this into Sheet2's code module (right click the sheet tab, etc., as above:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("A1") Is Nothing Then

        If Sheets("Sheet1").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale <> Sheets("Sheet1").Range("A2").Value Then

            Sheets("Sheet1").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = Sheets("Sheet1").Range("A2").Value

        End If

    End If

End Sub

The cell on Sheet2 that the user inputs to is D31 and the cell on Sheet1 containing the axis maximum is AU10. When I alter the code above to make it work with my sheets it gives me the error "subscript out of range".

OK, I thought as much. However, when I change A1 to D31 (which is the cell that changes on my sheet) and update the sheet numbers I get the error "the specified dimension is not valid for the current chart type" when I change cell D31 on Sheet2.

Also, I am no longer getting this message.