VBA to Select Chart Data

Ok, here is a sample. January is exactly how I need the rest of the tabs to be for their months.

Thank you,

Hi again,

Take a look at the attached workbook and see if it does what you need.

The workbook contains only a single graph with a series of Year and Month selection buttons. Clicking on these buttons will cause the graph to display the data for the selected Year/Month combination.

In general it is much simpler to "drive" a graph from a single range of cells, and to automatically or programatically populate that range with the required data, rather than to assign different ranges to different graphs. It also has the significant advantage that if style or formatting changes are necessary in the future, those changes can be made on a single graph rather than on twelve separate monthly graphs.

One other minor "benefit" has been included - when the 22-month period covered by the "Data" worksheet needs to be changed (e.g. from 2013-14 to 2015-16) only the starting date (e.g.1/3/2013) in cell B5 of the "Data" workshop should be changed (e.g. to 1/3/2015) - all of the other date cells in that column will be updated automatically. There's even a greyed-out row at the bottom of the table which will become enabled when the period includes a leap year.

I hope you find the attached workbook useful - please let me know how you get on with it.

Regards,

Greg M

Hi again,

Yes, the only VBA code is that contained in the "M01_ChartData" module.

This is one of those occasions where "native" Excel does about 97% of the work, and the VBA is there just to provide a prettier user-interface.

Aside from the VBA code, the application boils down to assigning the table on the "Graph Data" worksheet to the graph, and then using appropriate VLOOKUP formulas in the table to retrieve data for the month/year selected by the User. You can manually insert values into the "Selected Year" and "Selected Month" cells and observe how the values in the table are updated appropriately.

The VBA code used is simple, but elegant, in that all of the month buttons call the same "InsertMonth" macro, and both of the year buttons call the same "InsertYear" macro. The key to how it works is the use of the Application.Caller property which returns the name of the button which called the routine - the names of all of the month buttons contain the three-letter version of the associated month name, and this value is inserted into the "Selected Month" cell. A similar but very slightly different process is used for the year buttons.

Incidentally, here's a slightly nicer version of the "InsertYear" routine:

Please Login or Register  to view this content.

You can also use the above technique in conjunction with the month buttons if you prefer. The fundamental difference between the month buttons and the year buttons is that the text (i.e. month name) on the month buttons never changes, whereas the text (i.e. year) on the year buttons is determined by the ranges of the date cells listed on the "Data" worksheet.

Hope you find the above helpful - please let me know how you get on with it.

Regards,

Greg M