Waterfall Chart - Min & Max Scale Based On Cells

[GPerky]

Hi Everyone,

Firstly, I would like to set the scale on my Waterfall chart so it maximises the length of the bars - the Auto option on the Waterfall doesn't seem to do this very well.

Secondly, all my data changes based on a drop down for month, so I have a calculation to determine the min & max values.

I have then setup the VBA below on the sheet with the waterfall graph to trigger the change whenever cell B1 (the month) changes, however the scale doesn't seem to change??

Any help much appreciated.

Thanks

Graham

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$b$1" Then

ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("b19")
.MaximumScale = Range("b20")
End With

End If

End Sub

 

[Jeffrey Mahoney]

See my contribution in the Excel Articles

Waterfall Chart Automated

Awhile ago I was working for a company managing their budgets. My boss knew I was good with Excel and sent me a copy of a waterfall chart that was created by another person no longer employed by them. Apparently a few people tried to make...

www.mrexcel.com

 

[GPerky]

Thanks for your reply Jeffery. Much appreciated!!

I'm just looking for some simple VBA to "set" the Min & Max Scale based on a cell calculation I have done, however my VBA above doesn't seem to change it.

Would you have any idea why my code doesn't work?

 

[Jeffrey Mahoney]

Please try this. There are several changes. "Change" instead of "Selection_Change" should be used to test if the cell value has changed. Testing for the range is easier, IMHO, by using intersect. And you have to use the value of the cells, not the actual range itself. Sometimes VBA will let you get away with using just the cell reference and infer that you want to use the value; it should be stated always so not to get an ambiguous result.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Not Intersect(Target, Range("b1")) Is Nothing Then

    ActiveSheet.ChartObjects("Chart 1").Activate
    With ActiveChart.Axes(xlValue)
      .MinimumScale = Range("b19").Value
      .MaximumScale = Range("b20").Value
    End With

  End If
End Sub

 

[GPerky]

Hi Jeffery,

That worked perfectly!! Thankyou so much!! I would never have worked that one out!! (limited VBA knowledge)

It's annoying how the Waterfall Charts don't scale properly & utilize the whole area.

The other annoying thing is not being able to use the formula bar to reference a dynamic title & I see it's a bug in Excel from other posts. In my case, I just want to reference the Month on the end of the title using a simple formula.

Do you have any simple code for that?

Again, thanks so much for your help!!

Cheers

Graham

 

[Jeffrey Mahoney]

As far as I remember you can use a formula in a title bar. You must reference the sheet name or it won't work. Meaning you have to use a 3d formula even if it's on the same sheet as the chart.
=Sheet1!B4
with sheet names that have spaces:
='July 2020'!B4

In the cell you can blend text with Excel functions
="This is for the Month of "&Month(Now())

 

[Jeffrey Mahoney]

Oh, that formula would be something more like: "This is for the Month of "&text(Now(),"mmmm")

 

[GPerky]

Hi Jeffery,

Thanks for your reply.

I have the code for the title, but was rather looking for some code to change the Waterfall Graph Title.

Looks like it's a bug in Excel not being able to reference a cell for the title in a Waterfall graph, however someone mentioned in the below link to just use a text box which solved my problem.

Thanks again!!

Cheers

Graham

Waterfall chart tile linking to cell value

Hey Guys, Wondering if anyone has attempted linking a cell value to the chart title in Waterfall chart type. I know there isn't any directly available feature in MS Excel but may be could be an alternative using VBA. Thanks in advance guys.. This group is awesome with highly experienced...

www.excelforum.com

 

[Jeffrey Mahoney]

What I'm talking about only requires you to put a cell reference in for the title. Select the chart title and click inside the formula bar to enter the formula.

 

[GPerky]

Was your graph type a Waterfall Chart? It's that type of chart I was having issues with.