Y axis vba code EDIT to adjust automatically instead of clicking

erickguz

Board Regular
Joined
May 11, 2010
Messages
56
Hello, I found the following code from thespreadsheetguru that automatically adjusts the y axis on my chart.

I have charts throughout my file but only need the y axis adjustment on one sheet labeled "Home".

Presently, I need to click on the chart each time I change the stock symbol or time period.

Is it possible to make an adjustment to the following code, which works well, to automatically update the chart y axis whenever I change the symbol, or time period, eliminating the need to click on the chart? I change the setting hundreds of times throughout the day, and was looking to expedite the process.

Thank you,
Erick


Sub AdjustVerticalAxis()
'PURPOSE: Adjust Y-Axis according to Min/Max of Chart Data
'SOURCE: www.TheSpreadsheetGuru.com

Dim cht As ChartObject
Dim srs As Series
Dim FirstTime As Boolean
Dim MaxNumber As Double
Dim MinNumber As Double
Dim MaxChartNumber As Double
Dim MinChartNumber As Double
Dim Padding As Double

'Input Padding on Top of Min/Max Numbers (Percentage)
Padding = 0.1 'Number between 0-1

'Optimize Code
Application.ScreenUpdating = False

'Loop Through Each Chart On ActiveSheet
For Each cht In ActiveSheet.ChartObjects

'First Time Looking at This Chart?
FirstTime = True

'Determine Chart's Overall Max/Min From Connected Data Source
For Each srs In cht.Chart.SeriesCollection
'Determine Maximum value in Series
MaxNumber = Application.WorksheetFunction.Max(srs.Values)

'Store value if currently the overall Maximum Value
If FirstTime = True Then
MaxChartNumber = MaxNumber
ElseIf MaxNumber > MaxChartNumber Then
MaxChartNumber = MaxNumber
End If

'Determine Minimum value in Series (exclude zeroes)
MinNumber = Application.WorksheetFunction.Min(srs.Values)

'Store value if currently the overall Minimum Value
If FirstTime = True Then
MinChartNumber = MinNumber
ElseIf MinNumber < MinChartNumber Or MinChartNumber = 0 Then
MinChartNumber = MinNumber
End If

'First Time Looking at This Chart?
FirstTime = False
Next srs

'Rescale Y-Axis
cht.Chart.Axes(xlValue).MinimumScale = MinChartNumber * (1 - Padding)
cht.Chart.Axes(xlValue).MaximumScale = MaxChartNumber * (1 + Padding)

Next cht

'Optimize Code
Application.ScreenUpdating = True

End Sub
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,772
Maybe add some sheet change code related to stock symbol and/or time changes that would activate the relevant chart(s)? HTH. Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,127,563
Messages
5,625,528
Members
416,116
Latest member
Joemamasuka

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
Top