CODEWITHSUNDEEP
vbaexcel
M. Seaman
M. Seaman

Reputation: 25

Making my VBA Code update automatically when data is changed

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.

Upvotes: 1

Views: 3193

Answers (3)

micstr
micstr

Reputation: 5206

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.

excel axes gone wrong

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.

Upvotes: 0

user4039065
user4039065

Reputation:

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.

Upvotes: 0

Excel Hero
Excel Hero

Reputation: 14764

In the code module of the worksheet in question:

Private Sub Worksheet_Calculate()
    ScaleAxes
End Sub

Upvotes: 1

Related Questions