Excel VBA: manage axis options/scale in chart

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
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
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
0
LD16Author 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
0
Ejgil HedegaardCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Thank you very much. Tested and it works. Thank you very much for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.