Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Graphs - x axis label range

  1. #1
    Guest

    Default

    I have a simple graph where I plot certain water flows (y axis) against time (x axis) in units of months. The range that defines the x axis min. and max. limits changes very frequently, necesitating redefining the x axis label range manually. Is there a way of linking the upper and lower limits of the x range (or y range Min. & Max. for that matter) with a formula in the spreadsheet? I've tried using the INDIRECT function, but that doesn't work. Defining the Min. Max. and ranges to be graphed appears to only be do-able by setting them manually (or using the pointing method) through the Chart's source data or scale settings (or by defining a macro to do that for you).

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is a simple event macro that will set a chart's scale max and min whenever the value in cells named MaxXaxis and MinXaxis are edited. It assumes the chart is embedded on the sheet and that it is the first chart on the sheet.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("MinXaxis").Address Then
    ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = Target.Value
    ElseIf Target.Address = Range("MaxXaxis").Address Then
    ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = Target.Value
    End If
    End Sub

    This code must be placed in the worksheet's event code module. To do this, right-click on the worksheet tab, select View Code, and paste the code into the VBE code pane that appears.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Guest

    Default

    Thanks Damon!!!

  4. #4
    New Member
    Join Date
    Apr 2003
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Graphs - x axis label range

    Damon,

    What if my chart and the max & min I want to use are on different sheets? I would like to use two calculated cells on "Sheet1" to update the max and min "Y" scale on an embedded chart on "Sheet2".

    Cheers.

  5. #5
    New Member
    Join Date
    Apr 2003
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Graphs - x axis label range

    Damon,

    P.S. - I want to put the code in the click event of a button on the sheet with the data so that it doesn't run every time I activate the sheet.

  6. #6
    New Member
    Join Date
    Apr 2003
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Graphs - x axis label range

    Here's what I've tried so far (obviously, this doesn't work):

    Private Sub cmdScale_Click()
    Dim rngHigh As Range
    Dim rngLo As Range
    Set rngHigh = Worksheets("Sheet1").Range("E1")
    Set rngLo = Worksheets("Sheet1").Range("F1")
    Worksheets("Sheet2").ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = rngLo
    Worksheets("Sheet2").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = rngHigh
    End Sub

  7. #7
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Graphs - x axis label range

    Hi again wguidry,

    I don't see anything wrong with your code, although I do not see the necessity for the range object variables. What kind of an error did you get?

    Perhaps you should give this a try:

    Private Sub cmdScale_Click()
    Dim dblHigh As Double
    Dim dblLo As Double
    dblHigh = Worksheets("Sheet1").Range("E1").Value
    dblLo = Worksheets("Sheet1").Range("F1").Value
    Worksheets("Sheet2").ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = dblLo
    Worksheets("Sheet2").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = dblHigh
    End Sub

    Damon

  8. #8
    New Member
    Join Date
    Apr 2003
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Graphs - x axis label range

    I get the error - "Run-time error '1004': Unable to set the MinimumScale property of the Axis class.

    I bolded the line below where the debugger stops.

    Private Sub cmdScale_Click()
    Dim rngHigh As Double
    Dim rngLo As Double
    rngHigh = Worksheets("Weekly Calcs").Range("E1")
    rngLo = Worksheets("Weekly Calcs").Range("F1")
    Worksheets("Weekly Indicators").ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = rngLo
    Worksheets("Weekly Indicators").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = rngHigh
    End Sub

  9. #9
    New Member
    Join Date
    Apr 2003
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Graphs - x axis label range

    Damon,

    When I step into the subroutine in the debugger, I get the following message at the bold line below: "Run-time error '1004': Application-defined or object-defined error."

    Both of the variables populate with the correct values (I viewed them in the Watch window). Does it need to know which axis the values or for?


    Private Sub cmdScale_Click()
    Dim rngHigh As Double
    Dim rngLo As Double
    rngHigh = Worksheets("Weekly Calcs").Range("E1").Value
    rngLo = Worksheets("Weekly Calcs").Range("F1").Value
    Worksheets("Weekly Indicators").ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = rngLo
    Worksheets("Weekly Indicators").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = rngHigh
    End Sub

  10. #10
    New Member
    Join Date
    Apr 2003
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default GOT IT! - Graphs - x axis label range

    Damon,

    It was the (xlCategory). It wanted (xlValue). aaaaaahhhhhhh!!!

    OK, I can sleep now.

    Hey, thanks for all your help. You did all the heavy lifting on this one.

    Private Sub cmdScale_Click()
    Dim rngHigh As Double
    Dim rngLo As Double
    rngHigh = Worksheets("Weekly Calcs").Range("E1").Value
    rngLo = Worksheets("Weekly Calcs").Range("F1").Value
    Worksheets("Weekly Indicators").ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = rngLo
    Worksheets("Weekly Indicators").ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = rngHigh
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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