Set X-Axis Limits in VBA Loop

cc1987

New Member
Joined
Mar 4, 2016
Messages
16
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.

VBA Code:
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
784
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try
VBA Code:
        cht.Chart.Axes(xlCategory, xlPrimary).MinimumScale = MinNumber
        cht.Chart.Axes(xlCategory, xlPrimary).MaximumScale = MaxNumber
 

cc1987

New Member
Joined
Mar 4, 2016
Messages
16
Try
VBA Code:
        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.

VBA Code:
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

Well-known Member
Joined
May 16, 2017
Messages
784
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If you have an error that is relevant to your problem, you should post the details.
 

cc1987

New Member
Joined
Mar 4, 2016
Messages
16

ADVERTISEMENT

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

Well-known Member
Joined
May 16, 2017
Messages
784
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Mar 4, 2016
Messages
16

ADVERTISEMENT

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

VBA Code:
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

Well-known Member
Joined
May 16, 2017
Messages
784
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
See if this works
VBA Code:
        cht.Chart.Axes(xlCategory, xlPrimary).MinimumScale = CDbl(MinNumber)
        cht.Chart.Axes(xlCategory, xlPrimary).MaximumScale = CDbl(MaxNumber)
 

cc1987

New Member
Joined
Mar 4, 2016
Messages
16
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

Well-known Member
Joined
May 16, 2017
Messages
784
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This code is working as expected on my PC and will change the axes scale on multiple charts based on values in S3 & S4.
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top