Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to adjust scale of Chart Axis (Encligh/Excel XP)

    I would like to create a macro that would capture values (that change periodically) on a tab then use them to set the scale for both the X & Y axis of an Excel XP chart. I have a couple dozen charts in this file that woudl need this done. If I understand correctly, this would require VBA coding instead of basic macro programming.
    Any help would be appreciated.

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

    Re: Macro to adjust scale of Chart Axis (Encligh/Excel XP)

    Macro programming in MS Office (including Excel) *is* VBA programming.

    It certainly is possible to set the scale of the X and Y axis of charts in a macro, but before I or others can give meaningful suggestions, it would be nice to have more information:
    <UL><LI>Are the charts in separate chart sheets, or all in one worksheet, or in several worksheets?
    <LI>Are *all* charts in the workbook to be modified, or only some of them?
    <LI>Should the scale be the same for all charts to be modified, or different for each?
    <LI>You mention "capture values on a tab" - you mean in cells on a worksheet, or on a userform, or something else?[/list]

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to adjust scale of Chart Axis (Encligh/Excel XP)

    As Hans says we really need more information, but hopefully this macro will get you started. I created four named cells, xMin, xMax, yMin & yMax, that contain the new scale parameters for the chart. You can change/recalculate these parameters, select the chart, and run the newScale macro. If this isn't exactly what you want, post back with more details. We can loop through all of the charts in your workbook, but as Hans said, we need to know if the charts are on a sheet by themselves or embedded in a worksheet. I have attached a sample worksheet in case you need to see it work. HTH --Sam
    <pre>Option Explicit
    Sub newScale()
    If ActiveChart Is Nothing Then
    MsgBox "Please select the chart to modify first."
    Exit Sub
    End If
    With ActiveChart.Axes(xlCategory)
    .MinimumScale = [xMin]
    .CrossesAt = [xMin]
    .MaximumScale = [xMax]
    End With
    With ActiveChart.Axes(xlValue)
    .MinimumScale = [yMin]
    .CrossesAt = [yMin]
    .MaximumScale = [yMax]
    End With
    End Sub</pre>

    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    New Lounger
    Join Date
    May 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to adjust scale of Chart Axis (Encligh/Excel XP)

    Thanks for the help. I've got things figured out now.
    Thanks again.

  5. #5
    New Lounger
    Join Date
    May 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to adjust scale of Chart Axis (Encligh/Excel XP)

    That's a big help.
    I appreciate your time in assisting me with what will be a big time saver.
    Thanks again.

  6. #6
    New Lounger
    Join Date
    May 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to adjust scale of Chart Axis (Encligh/Excel XP)

    Hans,
    Thanks for your reply. I was able to obtain other help in this forum to solve my problem but I still appreciate the time you took to get back to me.
    Thanks again

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to adjust scale of Chart Axis (Encligh/Excel XP)

    How do I make this code work when the chart is not embedded on the worksheet?

    stuck

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to adjust scale of Chart Axis (Encligh/Excel XP)

    It works for me whether the chart is embedded or a chart sheet. What problem are you having? --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to adjust scale of Chart Axis (Encligh/Excel XP)

    My problem is ignorance/lack of VBA experience but I am making slow progress and I am pleased to report that I have got it working.

    Thanks,

    (un)stuck

Posting Permissions

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