How to create Excel (2007) Macro to automatically update graph x-axis range maximum to current date?

Question

I'm using Microsoft Office Excel 2007 and keep a graph that has membership (number) on the y-axis vs date (ie 10/20/2007) on the x-axis. My question is how I can get the graph to automatically read the current date and change (increase) the maximum value on the x-axis to become whatever the current date is. I'm not *that* experienced with Macros but if that's what I need to do then let me know. Thanks in advance.

Answer 1

The best way to do that is to use the Macro Recorder to help you out.

Turn the macro recorder on and have it recording as you change the date and values to get the chart to look the way you want it to look. Once you get it to look the way you want it too, then go back to the macro you just recorded and click the Edit button. This will pull up the Macro code. You'll want to find or figure out what the name is for the object that you changed the date value. The recorder should enter in that days date. For instance, today is 10/22/2007. If you entered the date today it would enter in the absolute date like that (10/22/2007), basically a hard number that doesn't change. What you need is a formula that gives you today's date. You need to change where it sets the value to 10/22/2007 to:

= Now()

That will give you the current day's date.

You then may want to take that macro code and copy it. In the Project Explorer window to the left side of Visual Basic Explorer you will see a list of the worksheets. Double-click on the sheet that has your chart. Then above the new blank code sheet that appears you will see two drop-down combo boxes. Change the one that says General by Selecting Worksheet from the list. Then change the right one to Activate. Paste your code from the macro you recorded and modified (everything inside the two lines that have the word Sub, don't include the Sub lines). And paste it to where the cursor is.

This should update the date whenever you activate the worksheet that has your chart. So the chart should be updated whenever anyone views it.

If you need more help, email me. It's not the easiest to explain online.

Answer 2

Excel Graph Macro

Answer 3

If you right click on the x axis then format axis you need to change the maximum and minimums and the value where it crosses the x axis, hopefully that should work...