Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Axis Scale (VBA/MS Excel/2003)

    I was wondering if there was a thread that had code that applied an entered cell value to the Format Axis - Scale interface. Or are there ideas from any takers.

    I would like to enter values in cells E2:E6 that would correspond to a scale that would match the color scheme of the Plot Area fill effect.

    For the attached example the "Category (X) axis Crosses at" value would have to be 10 less than the Alert value in cell E6 and the Maximum value would be 10 more than Exceeding value in cell E2.

    If the range of values in B2:B9 were 96-100, could the E2:E6 values still scale the axis correctly? Reference Sheet2 fo the workbook.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Axis Scale (VBA/MS Excel/2003)

    Try this code in the worksheet code module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cht As Chart
    Dim axs As Axis
    Dim dif As Double
    Dim unt As Double
    If Not Intersect(Target, Range("E2:E6")) Is Nothing Then
    Application.EnableEvents = False
    Set cht = Me.ChartObjects(1).Chart
    Set axs = cht.Axes(xlValue)
    dif = (Range("E2") - Range("E6")) / 20
    axs.MinimumScale = Range("E6") - dif
    axs.MaximumScale = Range("E2") + dif
    unt = axs.MajorUnit
    axs.MinimumScale = unt * Int(axs.MinimumScale / unt)
    axs.MaximumScale = unt * -Int(-axs.MaximumScale / unt)
    Application.EnableEvents = True
    End If
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Axis Scale (VBA/MS Excel/2003)

    Hans,
    That worked great. Thanks. A few questions and observations.

    When applying this code to another workbook, the code reference (Me.ChartObjects(1).Chart refers to the first Chart in the worksheet. That is, I have a worksheet with Chart5, Chart12 and Chart13. Chart5 is affected by the code you provided. How would I change the Chart numbering of each chart?

    And how would I apply this code in the same way to multiple charts on the same worksheet? I tried duplicating the Set cht = Me.ChartObjects(1).Chart and changing the Chart number reference, but that elementary thinking got nowhere. Any help would be greatly appreciated. I have attached the same worksheet

    I use the reference "VBA and Macros for Microsoft Excel" authored by "Mr. Excel" himself to gain a better foundation of the terms. I hope that isn't blasphemy here at Woody's I noticed that MaximumScale and MinimumScale method types aren't referenced. How does one find a full listing and description of objects and methods?

    What is the proper way to give credit to a Lounger when I apply code they have graciously offered?
    Would it be something like: 'Coded by HansV of Woody's Office Portal www.wopr.com on 2/29/08?
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Axis Scale (VBA/MS Excel/2003)

    If you want to refer to a specific chart, you can use its name instead of its index number: Me.ChartObjects("Chart 4").Chart
    If you would like to use a more meaningful name, you can assign it in the Immediate window in the Visual Basic Editor. For example, type

    ActiveSheet.ChartObjects(1).Name = "TrendChart"

    and press Enter. You can then use Me.ChartObjects("TrendChart").Chart in the code.

    If you want to run the same code for all charts on a sheet, you can use code like this; it also shows how you could acknowledge the Lounge:
    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Source: Woody's Lounge (http://www.wopr.com/cgi-bin/w3t/wwwthreads.pl)
    Dim obj As ChartObject
    Dim cht As Chart
    Dim axs As Axis
    Dim dif As Double
    Dim unt As Double
    If Not Intersect(Target, Range("E2:E6")) Is Nothing Then
    Application.EnableEvents = False
    For Each obj In Me.ChartObjects
    Set cht = obj.Chart
    Set axs = cht.Axes(xlValue)
    dif = (Range("E2") - Range("E6")) / 20
    axs.MinimumScale = Range("E6") - dif
    axs.MaximumScale = Range("E2") + dif
    unt = axs.MajorUnit
    axs.MinimumScale = unt * Int(axs.MinimumScale / unt)
    axs.MaximumScale = unt * -Int(-axs.MaximumScale / unt)
    Next obj
    Application.EnableEvents = True
    End If
    End Sub
    </code>
    There is no objection at all to using other resources than Woody's Lounge! We all do! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Axis Scale (VBA/MS Excel/2003)

    Just as additional info, you can also name the chart in the UI. Deselect the chart (e.g. by selecting a cell in the worksheet), hold Shift and then select the chartobject. You can then use the name box as normal to name the chartobject.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Axis Scale (VBA/MS Excel/2003)

    Thanks, nice tip!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Axis Scale (VBA/MS Excel/2003)

    You can get help within the Visual Basic Editor by placing the insertion point inside a keyword (or selecting the keyword) and pressing F1.
    Another useful tool is the Object Browser. Press F2 in the Visual Basic Editor to activate it. Type a word in the search box and press Enter. You can see all properties, methods and events of an object here. Click on an entry to see details in the bottom pane of the Object Browser, and press F1 to see the corresponding help subject.

Posting Permissions

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