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.

 

[Jon Peltier]

So, here is how the code on Sheet2 should look:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("[COLOR="Blue"]D31[/COLOR]") Is Nothing Then

        If Sheets("[COLOR="blue"]Sheet1[/COLOR]").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale <> Sheets("Sheet1").Range("[COLOR="blue"]AU10[/COLOR]").Value Then

            Sheets("[COLOR="blue"]Sheet1[/COLOR]").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = Sheets("Sheet1").Range("[COLOR="blue"]AU10[/COLOR]").Value

        End If

    End If

End Sub

Make sure the items in blue are correct. You've told me the two cell addresses, but not the name of the first sheet, which contains the formula and the chart.

 

[doublej41]

OK the problem was I was using the sheet number from the VBA properties tables, as opposed to the name I have given the sheet.

Changing Sheet1 to the sheet name solves the issue. As far as I can tell the 2 sheets are now working as I need them to.

Thanks for all of your help, Jon. I appreciate your patience.

 

[Jon Peltier]

Glad it finally works.