Change Maximum and Minimum Axis Values in Waterfall Chart based on If Statement Excel Vba

Question

In my excel workbook, my waterfall chart axis values change based on the values in two cells.
I have constrained my users to using a set cell as the lower value and another as the higher value. The problem is, they do not know which project value is lower or higher until they have selected it from a dropdown and see what value gets pulled in from another worksheet.

What I would like is for an IF statement to figure out which of the two values is larger and assign that value as the maximum value and the other as the minimum value.

My code:

Sub Worksheet_Calculcate()

   Dim objCht As ChartObject


   For Each objCht In Sheets("Price Bridge Chart").ChartObjects

      With objCht.Chart

         ' Value (Y) Axis
            With .Axes(xlValue)
            .MaximumScale = ActiveSheet.Range("$D$68").Value + 500000
            .MinimumScale = ActiveSheet.Range("$C$68").Value - 500000

         '   .MajorUnit = ActiveSheet.Range("$K$3").Value / 10 'Chart horizontal lines
            .MajorUnit = (.MaximumScale - .MinimumScale) / 10
            End With

      End With
   Next objCht
End Sub

I would probably have to add two additional variables but am trying to make the solution as simple as possible.

Remi Remi · Accepted Answer · 2018-04-12T16:14:50

It turned out to be simpler than I imagined. I assigned two extra variables and assigned the IF statement to them.

Sub Worksheet_Calculcate()

   Dim objCht As ChartObject
   Dim AxisOne As Long
   Dim AxisTwo As Long


   For Each objCht In Sheets("Price Bridge Chart").ChartObjects

   AxisOne = ActiveSheet.Range("$D$68").Value
   AxisTwo = ActiveSheet.Range("$C$68").Value

      With objCht.Chart

         ' Value (Y) Axis
            With .Axes(xlValue)
            If AxisOne > AxisTwo Then
            .MaximumScale = AxisOne + 500000
            .MinimumScale = AxisTwo - 500000
            Else
            .MaximumScale = AxisTwo + 500000
            .MinimumScale = AxisOne - 500000
            End If


         '   .MajorUnit = ActiveSheet.Range("$K$3").Value / 10 'Chart horizontal lines
            .MajorUnit = (.MaximumScale - .MinimumScale) / 10
            End With

      End With
   Next objCht
End Sub

Change Maximum and Minimum Axis Values in Waterfall Chart based on If Statement Excel Vba

1 Answers