Run time error 1004 with vba ticklabels.offset

[mhwolog]

Hello,
I'm trying to use VBA to alter the parameters of my chart. But specifically with the Ticklabels.Offset - I get "Run time error 1004 - Parameter not valid", and I can't work out why.

The code below works for the maximum scale and minimum scale, then gets an error where labelled.

ActiveSheet.ChartObjects("Chart 2").Activate
  With ActiveChart.Axes(xlValue, xlPrimary) 'y-axis of chart
    .MaximumScale = ActiveSheet.Range("AC63").Value
    .MinimumScale = ActiveSheet.Range("AE63").Value
        
  End With


With ActiveChart.Axes(xlCategory).TickLabels
 .Offset = -(ActiveSheet.Range("AE63").Value) / 7  ' error here
End With

I have also tried recording the macro, but when I run it again I get the error on the 5th line

    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.Axes(xlCategory).MajorGridlines.Select
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).TickLabels.Offset = 5000 'error here
    Application.CommandBars("Format Object").Visible = False

Thanks for your help

 

[bobsan42]

Select the chart and run this code:

Sub trychart()
    Dim i As Long
    On Error Resume Next
    Do While Err = 0
        i = i + 1
        ActiveChart.Axes(xlCategory).TickLabels.Offset = i
    Loop
    Debug.Print "i=" & i, Err.Number, Err.Description


End Sub

it will show you that .TickLabels.Offset does not accept values greater than 1000.

 

[mhwolog]

Thanks BobSan,
You are correct.