Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2013
    Thanked 0 Times in 0 Posts

    Auto adjust the vertical axis to the same scale for four graphs in the same workbook

    Hello to one and all:
    I have searched and tried adapting vba code found on the net, but I can't get anything to work. (no doubt due to my lack of coding experience). I have a template workbook containing four graphs. Once data is pasted into the template spreadsheet, four graphs are generated. Currently, the vertical axis scales can be different making comparisons diffficult. I am tired of having to manually adjust the scales, as I will ultimately have hundreds of data sets to analyze. I need to find code that will automatically adjust and make the scales the same for each graph, the max limit being determined by the data. I have attached a sample spreadsheet (no data) and a .docx containing code written by Ray Blake that I recently tried and failed to make work. In his code, I entered the four graph names and a cell (G35) containing the limit to be applied to the graphs. If anyone can solve this, I would be very grateful.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts
    This code will update any number of charts: just add the sheet names into the array. It will do all chartobjects on those sheets. It does not read the individual Min/Max from each page, but just reads the min and max from the "Determine max scale limit" worksheet.
    Option Explicit
    Sub UpdateScale()
      Dim vSheets As Variant
      Dim cht As ChartObject
      Dim dMax As Double
      Dim dMin As Double
      Dim x As Integer
      vSheets = Array("Tues Graph", "Wed Graph", "Thurs Graph", "3 Day Avg Table Graph")
      With Worksheets("Determine max scale limit")
        dMin = .Range("G4")
        dMax = .Range("G5")
      End With
      For x = LBound(vSheets) To UBound(vSheets)
        For Each cht In Worksheets(vSheets(x)).ChartObjects
          With cht.Chart.Axes(xlValue)
            .MinimumScale = dMin
            .MaximumScale = dMax
          End With
        Next cht
      Next x
    End Sub
    Note: Ray blake's code has the charts named, it does not work on the worksheet name. All the charts are on the activesheet.

    Hope this helps,
    Last edited by sdckapr; 2013-10-23 at 17:25. Reason: Changed min/max from integers to double just in case

  3. #3
    New Lounger
    Join Date
    Oct 2013
    Thanked 0 Times in 0 Posts
    Yes ! Thanks Steve. With one minor tweak it worked fine.
    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts