+ Reply to Thread
Results 1 to 5 of 5

charting using vba

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    charting using vba

    The attached workbook shows a chart and three sets of data, one of which was the data used for the chart. As you can see the chart has two data series one of which is displayed on a secondary axis so that the budget and actual amounts can be overlaid. When using the charting tool the two axes are often given different values, It is easy to override these manually to make them have the same values as I need in this case.
    There are 12 such charts to be shown, all with the same layout and with two equal vertical axes. I can only show 3 at once and I want the user to be able to choose which 3 to show.
    I have been using macros to develop some VBA to do this but have got stuck with the valuation of the secondary axis. I want to be able to say that having chosen the appropriate max value for the primary axis then the secondary axis should be the same.
    Is this possible? I can't use a macro to find out as obviously it just records the value I have used.
    John
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: charting using vba

    How are you currently assigning the max value to the primary axis? Assigning the same value to the max value of the secondary axis should be essentially the same statement, replacing xlsecondary for xlprimary. I would expect something like
    Please Login or Register  to view this content.
    Axis object help file: https://msdn.microsoft.com/en-us/vba...s-object-excel
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: charting using vba

    Mr Shorty, Many thanks for your interest.
    I am not setting the axis scales the charting procedure is. I am trying to use a macro to give me ways of coding for charts. This is what I am doing.
    I have 3 empty charts set out on a worksheet. I select one of these and choose a data range say Sheet1 a1:M13. I then discard the series I do not want say all the rows except SALES/FILM and BudgetSales. This produces a chart with the horizontal scale of months and a suitable vertical scale for the sales and budget sales columns which are in side by side pairs for each month. Next I select the Budget data series and make it on a secondary axis. I can then choose degree of overlap, separation, colour and transparency which give me the overlapped columns where you can see the budget and actuals, which is what I want. The problem is that often the charting system then gives the budget figures a different scale which I then change manually to the primary scale maximum. I cant code this because I only know the primary code by looking and I don't want my users to have to do that'.
    I need to know how to find the primary scale maximum so I can use that value for setting the secondary scale. Is this doable?
    John

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: charting using vba

    I'm not sure I understand the question -- perhaps because it seems so obvious to me that it feels like there is something about the question that I am missing.

    As explained in the help file for the maximumscale property (https://msdn.microsoft.com/en-us/vba...property-excel linked from above help file), the maximumscale property can return or set (read/write) the maximum scale property. In my sample code above, where I have "whatever you are doing to determine what the max axis value should be", you could put the maximumscale property of the primary axis, then assign that value to the maximumscale property of the secondary axis:
    Please Login or Register  to view this content.
    Is that all there is to your question, or is there more that I am missing?

  5. #5
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: charting using vba

    MrShorty,
    I have managed now to incorporate your code into my project and it is working exactly as I need.
    It was all that I wanted and before you provided the answer it seemed difficult enough to me! Many thanks for your help
    John

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Charting help!!!
    By Marz73 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 10-04-2016, 01:22 PM
  2. Excel 2007 : Charting
    By plmichel in forum Excel General
    Replies: 1
    Last Post: 11-04-2011, 01:39 PM
  3. [SOLVED] Charting
    By Jack in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-21-2006, 07:45 PM
  4. Charting help!
    By Dezos112 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-13-2006, 12:40 PM
  5. charting...
    By johnT in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-08-2005, 10:06 AM
  6. Custom charting - Stacked charting with a line
    By Randy Lefferts in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-03-2005, 12:06 AM
  7. [SOLVED] 3d charting
    By uriel78 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-28-2005, 04:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1