Set X-Axis Limits in VBA Loop

[cc1987]

I'm trying to set the x-axis limits for all charts in a worksheet equal to cell values thorugh a loop but I haven't been able to quite get it working yet.

Sub ChartSheets_Loop()

Dim cht As ChartObject

  Application.ScreenUpdating = False

      'Determine Maximum value
          MaxNumber = Sheet3.Range("S4").Value
        
        'Determine Minimum value
          MinNumber = Sheet3.Range("S3").Value

  For Each cht In Sheet3.ChartObjects
      
    'Rescale X-Axis
    cht.Activate
    cht.Chart.Axes(xlCategory).MinimumScale = MinNumber
    cht.Chart.Axes(xlCategory).MaximumScale = MaxNumber
  Next cht

   Application.ScreenUpdating = True

End Sub

I've looked at various threads and posts on similar but there's something I haven't got quite right with my code.

 

[rlv01]

Try

        cht.Chart.Axes(xlCategory, xlPrimary).MinimumScale = MinNumber
        cht.Chart.Axes(xlCategory, xlPrimary).MaximumScale = MaxNumber

 

[cc1987]

Try

        cht.Chart.Axes(xlCategory, xlPrimary).MinimumScale = MinNumber
        cht.Chart.Axes(xlCategory, xlPrimary).MaximumScale = MaxNumber

Thanks but no difference.

I've altered it slightlt yo this and there error changed.

Sub ChartSheets_Loop()

Dim cht As ChartObject

'Optimize Code
  Application.ScreenUpdating = False

      'Determine Maximum value
          MaxNumber = Sheet3.Range("S4").Value
        
        'Determine Minimum value
          MinNumber = Sheet3.Range("S3").Value

  For Each cht In Sheet3.ChartObjects
      
    'Rescale X-Axis
    cht.Activate
    cht.Chart.Axes(xlCategory).MinimumScale = MinNumber
    cht.Chart.Axes(xlCategory).MaximumScale = MaxNumber
  Next cht

'Optimize Code
   Application.ScreenUpdating = True

End Sub

Not sure is the error because the X-axis is a date variable

 

[rlv01]

If you have an error that is relevant to your problem, you should post the details.

 

[cc1987]

If you have an error that is relevant to your problem, you should post the details.

Apologies, I meant to include it.

"Method minimum scale of object axis failed" is the error

 

[rlv01]

Since the syntax is ok, I would guess that you are trying to use an illegal value for either MinNumber or MaxNumber (which are undeclared variables). If you are able to manually set the axes to what you want, another option available to you is to use the macro recorder to record a macro while you manually set the min/max values for the axes, and then inspect the code generated by the macro recorder to see how it is doing it.

 

[cc1987]

Thanks, I've tried the macro recorder previously and it works fine with the recorder returning this:

Sub Macro2()
'
' Macro2 Macro
'
    Range("S3").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.ChartObjects("Chart 44").Activate
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 42910
    Range("V143").Select
End Sub

The original macro is recognising 42910 as the value for MinNumber but for some reason doesn't allow it be used whereas it works fine with the recorded macro. .

 

[rlv01]

See if this works

        cht.Chart.Axes(xlCategory, xlPrimary).MinimumScale = CDbl(MinNumber)
        cht.Chart.Axes(xlCategory, xlPrimary).MaximumScale = CDbl(MaxNumber)

 

[cc1987]

No change.

The code works when an individual chart is referenced but one it's in a loop won't which seems strange to me.

 

[rlv01]

This code is working as expected on my PC and will change the axes scale on multiple charts based on values in S3 & S4.

Sub ChartSheets_Loop()
    Dim MaxNumber, MinNumber

    Dim cht As ChartObject
    Application.ScreenUpdating = False

    'Determine Maximum value
    MaxNumber = Sheet3.Range("S4").Value

    'Determine Minimum value
    MinNumber = Sheet3.Range("S3").Value

    If Not (IsDate(MinNumber) And IsDate(MaxNumber)) Then
        MsgBox "Inputs must be dates"
        Exit Sub
    Else
        MinNumber = CDate(MinNumber)
        MaxNumber = CDate(MaxNumber)
    End If

    If MinNumber >= MaxNumber Then
        MsgBox "Starting date must be before ending date"
        Exit Sub
    End If

    For Each cht In Sheet3.ChartObjects
        'Rescale X-Axis
        cht.Activate
        cht.Chart.Axes(xlCategory, xlPrimary).MinimumScale = MinNumber
        cht.Chart.Axes(xlCategory, xlPrimary).MaximumScale = MaxNumber
    Next cht

    Application.ScreenUpdating = True
End Sub