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]

I don't know why you got a type mismatch, but it should be xlCategory starting with X-L, not x1Category starting with X-one.

 

[doublej41]

Thanks for your reply.

I have changed the code to xlCategory and removed the variable declaration and it now works.

I am however having another issue; cell C1 contains a formula, the outcome of which I want to be the maximum value for my axis, however the axis does not update with the formula. The formula updates when the user pastes some raw data into the worksheet.

Is there a way to make this work where the target cell contains a formula instead of just a single user inputted value?

 

[Jon Peltier]

Oh, that explains the type mismatch. I hadn't noticed it was dimmed as a range. Oops. And it being declared (even improperly) prevented you from seeing the compile error that the x1Category keyword was undefined.

 

[doublej41]

Not sure where I lost the 'Dim' from the declaration.

Any thoughts on making it work when the target cell contains a formula?

 

[Jon Peltier]

A formula doesn't fire the Worksheet_Change event, so use the Worksheet_Calculate instead. You may want to save the previous value of the cell (e.g., in the next cell) and only update the chart if this value has changed.

 

[doublej41]

Under a Worksheet_Change event I get a compile error stating that the variable 'Target' is not defined.

The value in the cell will only ever be updated once, when the user first pastes their raw data.

 

[Jon Peltier]

In the worksheet code module, select Worksheet from the left dropdown, and Calculate in the right dropdown. Put the code in the procedure stub that appears in the module. There is no "Target" in Worksheet_Calculate, so you just have to write code that compares the cell to the first-time-only value.

 

[doublej41]

OK - Seems I am taking one step forward and two back.

Here is what I have under the Worksheet_Calculate event:

Private Sub Worksheet_Calculate()


With Sheet11.ChartObjects("Chart 4").Chart
    With .Axes(xlCategory)
        .MaximumScale = Sheet11.Range("$AU$10").Value
    End With
End With

End Sub

This is giving me another run time error, stating: Method 'MaximumScale' of object 'Axis' failed. I have multiple worksheets and therefore need to point the event at a single worksheet, rather than the active sheet. Annoyingly, this works on an example workbook I have with only one sheet and one chart.

Not sure I am using the correct method, VBA isn't something I am too familiar with.

 

[Jon Peltier]

What kind of chart is it?

Also since it supposedly will only be updated once, it hardly makes sense to write a procedure which will live forever in the workbook.