Waterfall Chart - Min & Max Scale Based On Cells

GPerky

New Member
Joined
Oct 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
See my contribution in the Excel Articles
 

GPerky

New Member
Joined
Oct 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Oct 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

GPerky

New Member
Joined
Oct 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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.
1609634800998.png
 

GPerky

New Member
Joined
Oct 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Was your graph type a Waterfall Chart? It's that type of chart I was having issues with.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,930
Messages
5,639,047
Members
417,066
Latest member
rhenman

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