Using custom scales for charts

michael25

New Member
Joined
Apr 24, 2014
Messages
5
Hello all,

I am currently working on a way to change the scales on charts. I have several tables on a sheet with corresponding charts, and I would like the chart axes scales to be selected automatically. However, when I select 'auto' in the format axis menu, it uses illogical numbers, displaying large empty areas above and below the highest and lowest points. I would like the lowest and highest point in the table to be the lowest and highest point in the chart.

To get the lowest and highest number of each table, I have used the following formulas:

=ROUNDDOWN(MIN($C$21:$E$31)*1,01;1)
=ROUNDUP(MAX($C$21:$E$31)*1,01;1)

For visual purposes, I add 1% to the number by multiplying the 'MIN' formula by 1,01 and I look for the nearest decimal value to which it should round up or down. These formulas work, but under the condition that each cell in the table contains a value. However, the data in the tables depends on the selections made from a list. When one or more selection fields are empty, the tables show zeros, and the Rounddown formula shows a zero as well. I would like this formula to exclude 0, but haven't found a way to achieve this yet.

To automatically change the minimum and maximum values used by a chart, I have used a code I found on the internet:

Sub UpdateScale()

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

ActiveSheet.ChartObjects("Chart 2").Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("G33").Value
.MaximumScale = Range("G34").Value
End With

ActiveSheet.ChartObjects("Chart 3").Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("G46").Value
.MaximumScale = Range("G47").Value
End With

ActiveSheet.ChartObjects("Chart 4").Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("G59").Value
.MaximumScale = Range("G60").Value
End With

ActiveSheet.ChartObjects("Chart 5").Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("G72").Value
.MaximumScale = Range("G73").Value
End With

End Sub

The code functions as well, but has to be executed manually each time the corresponding data for the chart changes. I would like this to be done automatically, so that every time a change is made to the table, the chart automatically uses the new Minimum and Maximum values.

Any ideas on solving these 2 issues?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
Try this as the formula to ignore zeros.
Confirm with Ctrl+Shift+Enter.

=ROUNDDOWN(MIN(IF($C$21:$E$31<>0,$C$21:$E$31,""))*1,01;1)


Use something like this to trigger your macro when the user changes cell A1. Change the cell reference to what the user changes to affect the "list".

Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Target, Range("[COLOR=#ff0000]A1[/COLOR]")) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Call[/COLOR] UpdateScale
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

To install this code, right-click on the sheet tab and select View Code. Paste the code in the worksheet's code module.
 

michael25

New Member
Joined
Apr 24, 2014
Messages
5
Try this as the formula to ignore zeros.
Confirm with Ctrl+Shift+Enter.

=ROUNDDOWN(MIN(IF($C$21:$E$31<>0,$C$21:$E$31,""))*1,01;1)


Use something like this to trigger your macro when the user changes cell A1. Change the cell reference to what the user changes to affect the "list".

Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Target, Range("[COLOR=#ff0000]A1[/COLOR]")) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Call[/COLOR] UpdateScale
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

To install this code, right-click on the sheet tab and select View Code. Paste the code in the worksheet's code module.

The rounding down now works, thanks for this. However, the auto update doesn't work. I have a similar Private Sub Worksheet_Change macro, it is the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Rng As Range
Dim Idx As Nsm

On Error Resume Next ' Target.Cells.Count = Overflow
If Target.Cells.Count > 1 Then Exit Sub
If Err Then Exit Sub

On Error GoTo 0
Set Rng = Selector(Target, Idx)
If Idx = NsmNone Then
DisableControlEvents = True
With CbxSelect
.Visible = False
.Value = vbNullString
End With
DisableControlEvents = False
Exit Sub
End If

With Target
If Trim(.Value) = vbNullString Then
SetApplication False
SelectFirstCell Rng, Target
SetApplication True
End If
End With
SetCbxSelect Target, Idx
End If

End Sub

I think this needs to be adapted. Someone else designed it for me, the macro from my earlier post was added. How can I adapt the code so that it takes the UpdateScale into consideration?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
I didn't test it but perhaps something like this. Again, change A1 to suit.

Code:
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Target, Range("[COLOR=#ff0000]A1[/COLOR]")) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] [color=darkblue]Call[/color] UpdateScale
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_SelectionChange([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    
    [color=darkblue]Dim[/color] Rng    [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Idx    [color=darkblue]As[/color] Nsm
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]    [color=green]' Target.Cells.Count = Overflow[/color]
    [color=darkblue]If[/color] Target.Cells.Count > 1 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]If[/color] Err [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    [color=darkblue]Set[/color] Rng = Selector(Target, Idx)
    [color=darkblue]If[/color] Idx = NsmNone [color=darkblue]Then[/color]
        DisableControlEvents = [color=darkblue]True[/color]
        [color=darkblue]With[/color] CbxSelect
            .Visible = [color=darkblue]False[/color]
            .Value = vbNullString
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        DisableControlEvents = [color=darkblue]False[/color]
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]With[/color] Target
        [color=darkblue]If[/color] Trim(.Value) = vbNullString [color=darkblue]Then[/color]
            SetApplication [color=darkblue]False[/color]
            SelectFirstCell Rng, Target
            SetApplication [color=darkblue]True[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    SetCbxSelect Target, Idx
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 

michael25

New Member
Joined
Apr 24, 2014
Messages
5

ADVERTISEMENT

Still no luck, as I have no clue what I have to substitute A1 with. There are very advanced codes implemented that are not traceable back to one specific cell or range of cells. There are 4 modules of code next to the specific module for the worksheet. Too bad this forum doesn't allow uploading files, which makes it kind of useless for advanced workbooks.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
What cell(s) on the sheet does the user change that changes the chart the data in C21:E31? Not the cells with formulas. What does the user change?
 

michael25

New Member
Joined
Apr 24, 2014
Messages
5
What cell(s) on the sheet does the user change that changes the chart the data in C21:E31? Not the cells with formulas. What does the user change?

There are several selection boxes in B4:B18. The tables (input for the charts) contain formulas that get the required data through formulas. The selection boxes in B4:B18 are so-called Comboboxes, and the tables and charts are updated each time a selection has been made in one of these boxes. The ranges of the Comboboxes are: B4:B8,B9:B10,B11:B13,B14:B18. In the code they are named: CbxRanges.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
Try this...

Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
     [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Target, Range("[COLOR=#ff0000]B4:B8,B9:B10,B11:B13,B14:B18[/COLOR]")) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Call[/COLOR] UpdateScale 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 

Forum statistics

Threads
1,136,277
Messages
5,674,799
Members
419,526
Latest member
ranjit446

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