Using custom scales for charts

[michael25]

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?

 

[AlphaFrog]

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".

[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]

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".

[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]

I didn't test it but perhaps something like this. Again, change A1 to suit.

[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]

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]

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]

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]

Try this...

[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]