Macro that Adjust All chart ranges with Data Validation


New Member
Feb 16, 2017

I need help writing a macro that adjust my axis min and max bounds automatically with data validation.

I have the macro below currently but it only makes adjustments to the current selection. How can I get the macro to automatically run every time I change the drop down choices.

Sub AdjustVerticalAxis()
'PURPOSE: Adjust Y-Axis according to Min/Max of Chart Data

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


New Member
Feb 16, 2017
I figured it out.

1) Click on the Developer tab.
2) Click on the Visual Basic icon.
3) On the left pane window, double click the sheet where you need your code to run.
4) Now, at the top of the code window you will see... (General) with a drop down, and (Declarations) with a drop down.
5) Click the drop down by (General) and select Worksheet.
6) Now in the code window you will see.... Private Sub Worksheet_SelectionChange(ByVal Target As Range)
7) Remove the word "Selection". You want to remove "Selection" because that means when you click on a cell in the worksheet something will happen. You do not want that, you want to enter a value in D10. It should now read...Private Sub Worksheet_Change(ByVal Target As Range)
8) Now this is where you want the code...

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$10" Then

Call MyMacro

End If

End Sub

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...