Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
Sign in to follow this  
Kirabo

Changing Chart Y-Axis Maximum with VBA

Question

Hey, 

 

I have 2 charts on my sheet (one bar & one line), and I need to make sure their MAX values on the Y-Axis are the same. 

 

They are using different datasets, so I need the highest Y-Axis to apply to both so that I can overlay the charts over one another on the sheet. I can't find a way to do this with the GUI, is this something VBA can do?

 

TIA

Kirabo

Share this post


Link to post
Share on other sites

6 answers to this question

Recommended Posts

  • 0

Hey Kirabo, 

 

I thought that may have been the case! It's always a good idea to rename your charts however, as this makes things easier when modifying them via VBA.

 

You can make use of the CEILING function to round up to the nearest 10, and I have modified the code slightly to just look at the entire columns of B:C. 

Sub ChangeAxisScales_1Line()
'// Source: www.ExcelWTF.com
'// Purpose: Set Max Y-Axis using MAX of 2 x datasets
Dim Max1 As Long, Max2 As Long
Dim ws As Worksheet

    Set ws = Sheets("Utilization")
    With ws
        Max1 = WorksheetFunction.Max(.Range("B:B"))
        Max2 = WorksheetFunction.Max(.Range("C:C"))
        
        If Max1 > Max2 Then ' Change the chart names below!
            .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max1, 10)
            .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max1, 10)
        Else
            .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max2, 10)
            .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max2, 10)
        End If
    End With
End Sub

Give it a try and let me know how you get on 

 

Thanks

Caleeco

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

Hi Kirabo, 

 

As far as I know, this is not possible using the Excel interface. I believe Microsoft are looking to introduce equation controlled axis' in a future release of Excel, but for now we have VBA :D

 

Can you let me know the following:

  1. Your sheet name
  2. The names of the 2 charts
  3. and where the data for each charts if (Cell references eg. C1:C20)

Cheers
Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hey

 

Where do I find out the name of the chart? is it the title?

- My two datasets are in columns B2:B8 & C2:C15.

- My sheet name is "Utilization"

 

Thanks for your help!

Share this post


Link to post
Share on other sites
  • 0

Hey Kirabo, 

 

If you are using Excel 2013 or greater, you can just click on the chart and then the Chart Name will be displayed in the Top Left of the screen (Next to the formula bar). You can then edit the code below to suit, in my example i have called them 'Chart1' and 'Chart2'.

Sub ChangeAxisScales_1Line()
'// Source: www.ExcelWTF.com
'// Purpose: Set Max Y-Axis using MAX of 2 x datasets
Dim Max1 As Long, Max2 As Long
Dim ws As Worksheet

    Set ws = Sheets("Utilization")
    With ws
        Max1 = WorksheetFunction.Max(.Range("B2:B8"))
        Max2 = WorksheetFunction.Max(.Range("C2:C15"))
        
        If Max1 > Max2 Then ' Change the chart names below!
            .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = Max1
            .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = Max1
        Else
            .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = Max2
            .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = Max2
        End If
    End With
End Sub

Let me know how you get on

Thanks

 

Caleeco

 

 

 

 

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

haha turns out my charts ARE called Chart1 & Chart2 anyway xD

 

That's cool, seems to work great! However, is it possible to change it so that the axis find the biggest number in my dataset and then rounds up to the nearest 10?

 

Also how can i change it so that columns B:C can be dynamically filled with more data and I wont have to keep editing the code?

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

Thanks so much for this good sir!

 

Worked like a charm B| I have other improvements to make to my dashboard so I'll pop by again soon!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×