Stock Chart to change Y Axis automatically

erickguz

Board Regular
Joined
May 11, 2010
Messages
58
I am good with Excel but have not much experience or knowledge with VBA.

My 10mb files kept crashing. I have a powerful laptop.

I read many sites about chart axis, including peltier. I also read that macros are the cause of many issues, and I guess my apparently faulty macros were the cause. I went from a 10mb file that crashed everyday to a 35mb file that never crashes. In addition, my 35mb macro-free file loads in 30 seconds vs. 4 minutes for my previous 10mb macro version.

I use my spreadsheet to plot charts for stocks. Unfortunately, I have to enter the MIN and MAX for each chart if I want to look at the prices in greater detail. For example, Excel will plot a stock with a range of $30 - $35 starting at 0, so it almost looks like a straight line. Ideally I would prefer a chart range from $29 - $36.

My old macro that I found online worked but may have been the cause of the problem. I found the following "snippet" online. However, I have to click the chart each time, and then run it each time I load a new chart. Also, it doesn't exactly work all of the time. Is there something that I can add so that it automatically selects the one chart on the sheet and assigns the updated y axis MIN (A2) and MAX (A3) automatically?

Thank you,
Erick



If ActiveChart Is Nothing Then ' Error trap to ensure a chart is selected
MsgBox "Please: Select a chart and try again.", vbExclamation

Else

ActiveChart.Axes(xlValue).MinimumScale = Range("A2") 'Set min scale to cell A2
ActiveChart.Axes(xlValue).MaximumScale = Range("A3") 'Set max scale to cell A3
ActiveChart.Axes(xlValue).MajorUnit = Range("A4") 'Set major tick to cell A4
ActiveChart.Axes(xlValue).MinorUnit = Range("A5") 'Set minor tick to cell A5

End If
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top