none
VB to set x-axis = A9 RRS feed

  • Question

  • Good morning,

    I have a series of charts which are on two sheets, in total 30 charts.  I would to set the values on the x-axis = to the value in cell A9, just to avoid having to into each chart every month to modify.

    The x-axis is months of the year (Jan, Feb).  In cell A9 I can either display the month as letters or as a number (for the moment its a number).

    I have a current macro which works fine on one off charts.  Would someone be able to help me modify this macro so that it updates a list of charts to set the x-axis equal to cell A9 in sheet BaseData?

    For an example;

    On sheet BaseData I have chart called Trends, Products, Plants, People

    On sheet called Results I have charts called Dates, Suppliers, Cost, Return

    Existing code below.

    Thanks for your help!

    Sub Graphs()
    '
    Dim x As Integer
    Dim G As String

    Month = Range("AE1") * 2

    For i = 2 To 6
    G = "Chart " & i

        ActiveSheet.ChartObjects(G).Activate
        ActiveChart.PlotArea.Select
            For x = Mois + 1 To 22
                ActiveChart.ChartGroups(1).FullCategoryCollection(x).IsFiltered = True
                ActiveChart.ChartGroups(2).FullCategoryCollection(x).IsFiltered = True
            Next
    Next
    End Sub

    Friday, September 23, 2016 9:18 PM

Answers

  • >>>My issue is how do i change from G = "Chart " & i to a list of chart names (Trends, Products, Plants, People, Dates, Suppliers, Cost, Return) which can be on several sheets in the workbook, (each chart name is used only once in the workbook)?

    According to your description, sorry, I am still not clear about your issue, do you want use VBA to create collection to save chart names? If so, you could use Array,ArrayList and Dictionary etc., refer to this link:

    http://analystcave.com/excel-vba-dictionary-arrays-and-other-data-structures/

    and this issue would be more related to VBA, you could post any issue about VBA on MSDN forum for Visual Basic for Applications (VBA)

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Friday, September 30, 2016 5:37 AM
    • Marked as answer by David_JunFeng Wednesday, October 5, 2016 1:40 PM
    Thursday, September 29, 2016 6:50 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, September 26, 2016 5:36 AM
  • Hi Lind89,

    According to your description, please correct me if I have any misunderstandings on your question, you could use Series.XValues property to set X axis programmatically on a chart:
    Charts("Chart1").SeriesCollection(1).XValues = _ 
     Worksheets("Sheet1").Range("B1:B5")
    For more information, please refer to Series.XValues Property (Excel)

    Thanks for your understanding.
    Tuesday, September 27, 2016 6:01 AM
  • Hi,

    Sorry but maybe my description was not clear enough.

    My issue is how do i change from G = "Chart " & i to a list of chart names (Trends, Products, Plants, People, Dates, Suppliers, Cost, Return) which can be on several sheets in the workbook, (each chart name is used only once in the workbook)?

    Thanks

    Wednesday, September 28, 2016 8:51 PM
  • >>>My issue is how do i change from G = "Chart " & i to a list of chart names (Trends, Products, Plants, People, Dates, Suppliers, Cost, Return) which can be on several sheets in the workbook, (each chart name is used only once in the workbook)?

    According to your description, sorry, I am still not clear about your issue, do you want use VBA to create collection to save chart names? If so, you could use Array,ArrayList and Dictionary etc., refer to this link:

    http://analystcave.com/excel-vba-dictionary-arrays-and-other-data-structures/

    and this issue would be more related to VBA, you could post any issue about VBA on MSDN forum for Visual Basic for Applications (VBA)

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Friday, September 30, 2016 5:37 AM
    • Marked as answer by David_JunFeng Wednesday, October 5, 2016 1:40 PM
    Thursday, September 29, 2016 6:50 AM