Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2013
    Posts
    4
    Thanks
    0
    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
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    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.
    Code:
    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,
    Steve
    Last edited by sdckapr; 2013-10-23 at 18:25. Reason: Changed min/max from integers to double just in case

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

Posting Permissions

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