Making my VBA Code update automatically when data is changed

Question

I am attempting to use VBA to set chart axis parameters based on data. This is for work and I am going to lock the spreadsheet as a template so others can fill in data but the chart will be made for them. Depending on the test they are running, the axes may need to be different. I have a code for this that works perfect, but it doesn't update automatically when data changes, I have to refresh it manually. Here is the code:

Sub ScaleAxes()

With Application.ActiveChart.Axes(xlCategory, xlPrimary)

.MinimumScale = ActiveSheet.Range("T4").Value

.MaximumScale = ActiveSheet.Range("T3").Value

.MajorUnit = ActiveSheet.Range("T5").Value

End With

With Application.ActiveChart.Axes(xlValue, xlPrimary)

.MinimumScale = ActiveSheet.Range("U4").Value

.MaximumScale = ActiveSheet.Range("U3").Value

.MajorUnit = ActiveSheet.Range("U5").Value

End With

End Sub

The cells that are referenced in the code contain functions that are dependent on data entry. I would like this code to refresh by itself whenever the cells it depends on change.

TBH, this is a little unclear on WHY it is important. It the values are set to U3:U5 then changing one of them will instantly update the chart. It is only if the values were referenced to another range originally that this becomes important. — user4039065
– user4039065, Commented Oct 19, 2015 at 20:10

Excel Hero · Accepted Answer · 2015-10-19 20:01:48Z

1

In the code module of the worksheet in question:

Private Sub Worksheet_Calculate()
    ScaleAxes
End Sub

answered Oct 19, 2015 at 20:01

Excel Hero

14.8k4 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user4039065 Over a year ago

How is that going to find the right ActiveChart when new values are typed into T3:U5?

Excel Hero Over a year ago

Likely only has one chart. Also the ranges referenced in the code have formulas in them. I was not vouching for the OP routine only answering the question posted.

user4039065 Over a year ago

OK, I get it. Formulas that change due to values from another worksheet changing. Makes sense that way.

Excel Hero Over a year ago

That's what I got out of it. Could be a lot more clear.

M. Seaman Over a year ago

To clarify, the referenced cells change based on formulas that reference another sheet.

|

score 0 · Accepted Answer · 2015-10-19 20:18:34Z

You are going to have to be more definitive on the identification of the ActiveChart. If there is only one then it could be referred to by its index number but the name of the chart would be better. The following assumes a single chart on the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("T3:U5")) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        With Me.ChartObjects(1)
            With .Axes(xlCategory, xlPrimary)
                .MinimumScale = Range("T4").Value
                .MaximumScale = Range("T3").Value
                .MajorUnit = Range("T5").Value
            End With
            With .Axes(xlValue, xlPrimary)
                .MinimumScale = Range("U4").Value
                .MaximumScale = Range("U3").Value
                .MajorUnit = Range("U5").Value
            End With
        End With
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

A Worksheet_Change event macro belongs in a worksheet code page, not a Module code page. You can get to the worksheet's code page by right-clicking the worksheet's name tab and choosing View Code.

micstr · Accepted Answer · 2015-10-19 20:27:13Z

To add to @Excel Heros answer. I had a similar problem but you may find at times Excel's Auto scale option does not work so well for some charts (depending on range of data), and it adds in the origin (zero) and you are left with a tiny squashed data block plotted on top.

See Peltier for an excellent explanation of how Excel works out axes range.

Macro Buttons

You can always use a Macro Button on the worksheet that users can use to Reset the Axes.

I used a Set Axes Minimum Button to switch axes to a minimum in a table / or set by user

And a Reset Axes Range for my chart to switch back to Auto.

CODE

This code below was specific to my task but you might be able to salvage some ideas from it.

Reset the axis back to auto

Sub btnChartAxisResetRange()
    ' SET chart object name

    Call ChartResetAxis("chtRange")
End Sub

Sub ChartResetAxis(strChartname As String)
' Reset the lower y axis of chart back to Auto
'
' INPUTS
'  Relies on chart object name
'
    ActiveSheet.ChartObjects(strChartname).Activate
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScaleIsAuto = True

End Sub

Tweak the axis minimum to another value

In this case I had a table of minimums for certain price charts where I know in a weekly pack I had to override the minimum axis issue when I did a batch run. This prevented charts like I show in figure above.

Sub GetChartMinimumAxisTweaks()
'  Change minimum axis scale if set to zero and looks too small
'  Set the lower y axis of chart so the lower value does not default to zero
'  Uses a table manually set up
'
' INPUTS/PREREQUISITES
'  Relies on manual data set up in rng1PagerAxesOverrideTable with Minimum scales for either chart
'
' CALLS
'  ChartTweakAxis(strChartname As String, minvalue As Double)

  Dim minaxisvalue As Double

  ThisWorkbook.Activate

  ' Get axis value. Using offsets of range
  ' But if no value then need to reset

  ' Chart of Price
  minaxisvalue = Val(ThisWorkbook.Sheets("ReviewCalcPage").Range("rng1PagerAxesOverrideCheck").Offset(0, 1).Value)

  Call ChartTweakAxis("chtSTPerformance", minaxisvalue)

End Sub

Sub ChartTweakAxis(strChartname As String, minvalue As Double)
'  Change minimum axis scale if set to zero and looks too small
'  Set the lower y axis of chart so the lower value does not default to zero
' INPUTS
'  Relies on chart object name and manual value to set Minimum scale too
'  If minvalue is zero then Reset rather to Auto
'

     If minvalue = 0 Then
        Call ChartResetAxis(strChartname)
    Else
        ' Set axis
        ActiveSheet.ChartObjects(strChartname).Activate
        ActiveChart.PlotArea.Select
        ActiveChart.ChartArea.Select
        ActiveChart.Axes(xlValue).Select
        ActiveChart.Axes(xlValue).MinimumScale = minvalue
    End If
End Sub

Hope this helps if you run into axes issues! Bit off topic but trying to help.

Collectives™ on Stack Overflow

Making my VBA Code update automatically when data is changed

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related