We help IT Professionals succeed at work.

Changing X-axis ranges in Excel 95

dwcurths
dwcurths asked
on
295 Views
Last Modified: 2006-11-17
I need to be able to automatically change the X-axis range for several Excel charts.  I typically plot time (in month intervals) on the X-axis and I need the charts to cover the last 24 months.  I am currently using the scatter plot format.  I would like to use the NOW() function to generate the current date in one of the spreadsheet cells and back calculate the starting date of the required 24 month period.

So my question is...  How can I automatically change the minimum and maximum scaling factors to be set to these values?  I would perferred a simple Excel macro since I am not very famaliar with VB.  Also, the sample code (macro or VB) would be greatly appreciated.
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
To: calacuccia

Must apologize for any problems with question.  This first time I have posted a question, so I really don't know how this site fundamentally works.

Since I work for the "Federal Government", they get pretty upset if I use my work related equipment for internet access.....  So I just now had a chance to review your comment using my personal account.  

To answer your question, the date format should be/can be converted to serial number format (i.e. 36586.5 equals 03/01/2000 12:00), however usually the X-range cell format is MMM-YYYY.  

I'll try your proposed macro tomorrow (03/02/2000) at work.  It appears that it should work (or that I should be able to get it work) with methods that you indicated.  

If it works, then I'll close the question to your response.

Thank you (in advance)

Author

Commented:
To:  calacuccia

Macro worked just fine.  I modified the code and added  a seperate spreadsheet titled "Chart Axis Controls".  On this spreadsheet, I allowed the user to over ride the default span of 24 months by enter desired start and stop dates in selected cells.  Then in other cells, I used the worksheet "if" function to determine if user had entered values other than default period.  The macro then went to the "if" cells and set parameters "a" and "b" to the values.  Wrote some more code which automatically reset the chart to the default values and left user back at "Chart Axis Controls".  Everything works great.  Thanks for the ideas.
Hi dwcurths,

Glad I could help you. From the modifications you made I see you have the right mind about solving problems, you prefer to know how and adjusting yourself so that you can do it yourself the next time. Sometimes, people on this site just ask for a pack & go solution and don't bother bout understanding what made it work. Those people often get stuck is there's a tiny error in the code.

Another tip, if you want to make macros in the future and have no clue which commands, objects... to use, a godd start is often to do manually what you want the macro to do automatically later while recording a macro.

Excel will write large parts of code, of which a lot has to be adjusted to specific needs, but often the basic and important macro commands are generated.

To record a macro, go to Tools/Macro/Record New Macro, enter some info in following dialog boxes, click OK, start doing manually what you want to be generated, and click the 'Stop button' when finished (or go to Tools/Macro/Stop Recording).

Then hit Alt+F11 to enter the VBA editor to look at generated code and start fixing it for your needs. I've learned tons of things this way.

Cheers

Calacuccia

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.