Excel VBA: manage axis options/scale in chart

Luis Diaz
Luis Diaz used Ask the Experts™
on
Hello Experts,
I have the following excel timeline attached.
I was wondering how can I manage directly in the sheet and link dynamically with the chart axis options/scale in terms of bounds, minimum, maximum and in terms of units, major minor. I read the following article and
https://peltiertech.com/chart-udf-control-axis-scale/
I think there is a function or a vba procedure which allows me to do this.
Could you please provide expertise?
Thank you very much for your help.
Timeline-Example.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The link has the solution, and when inserted in the sheet, it does what you want.
Use Shift+F3 to see the function argument dialog.
Timeline-Example.xlsm
Luis DiazIT consultant

Author

Commented:
Great, thank you very much for your help.
Two additional requirements to cover the final need.
1-Is it possible to refer to active sheet in the formula so if I want to copy the sheet it would be automatically updated with active sheet? I added in cell O30 the formula to get active sheet. However I don't know how should I refer it to the axis formula.
2-In Module 1 I added the procedure to export to pdf the chart. I was wondering how we can export in landscape orientation and have date stamp at the left corner.

Please find attached latest version.

Thank you again for your help.
Timeline-Example-V2.xlsm
1 You use the formula like any other formula in Excel, so when the sheet reference (first argument) point to O30, that value is used.

2 The page orientation must be set specifically for the chart.
Select the chart, and then set the orientation to landscape.

A text box with reference to cell B2 can insert the date on the chart.
Make a textbox anywhere on the sheet, and in the formula line start a formula with = ,then select B2, and Enter.
Copy the textbox, select the chart, and insert with Ctrl+V.
It will be in the upper left corner.
Move the textbox to desired position.
Then the textbox are anchored to the chart, and will move with the chart.
Delete the textbox on the sheet.
I think there are other methods to anchor the textbox to the chart, but don't remember how, above method works.
Timeline-Example-V2.xlsm
Luis DiazIT consultant

Author

Commented:
Thank you very much. Tested and it works. Thank you very much for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial