Excel: automatically update chart axes and other properties with VBA



I found these 2 alternatives of code most essential:

  • Only update when there is a change to the axis limits cells:
    • Option Explicit
      Private Sub Worksheet_Change(ByVal Target As Range)
          With ActiveSheet.ChartObjects("Chart 1").Chart
              Select Case Target.Address
                  Case "$K$3"
                      .Axes(xlCategory).MinimumScale = Target.Value
                  Case "$K$4"
                      .Axes(xlCategory).MaximumScale = Target.Value
                  Case "$K$5"
                      .Axes(xlCategory).MajorUnit = Target.Value
                  Case "$L$3"
                      .Axes(xlValue).MinimumScale = Target.Value
                  Case "$L$4"
                      .Axes(xlValue).MaximumScale = Target.Value
                  Case "$L$5"
                      .Axes(xlValue).MajorUnit = Target.Value
              End Select
          End With
      End Sub
  • Update whenever the worksheet is recalculated:
  • Option Explicit
    Private Sub Worksheet_Calculate()
        Dim cht As Chart
        Dim wks As Worksheet
        Set wks = ActiveSheet
        Set cht = wks.ChartObjects("Chart 1").Chart
        If wks.Range("$K$4").Value <> cht.Axes(xlCategory).MaximumScale Then
            cht.Axes(xlCategory).MaximumScale = wks.Range("$K$4").Value
        End If
        If wks.Range("$K$3").Value <> cht.Axes(xlCategory).MinimumScale Then
            cht.Axes(xlCategory).MinimumScale = wks.Range("$K$3").Value
        End If
        If wks.Range("$K$5").Value <> cht.Axes(xlCategory).MajorUnit Then
            cht.Axes(xlCategory).MajorUnit = wks.Range("$K$5").Value
        End If
        If wks.Range("$L$4").Value <> cht.Axes(xlValue).MaximumScale Then
            cht.Axes(xlValue).MaximumScale = wks.Range("$L$4").Value
        End If
        If wks.Range("$L$3").Value <> cht.Axes(xlValue).MinimumScale Then
            cht.Axes(xlValue).MinimumScale = wks.Range("$L$3").Value
        End If
        If wks.Range("$L$5").Value <> cht.Axes(xlValue).MajorUnit Then
            cht.Axes(xlValue).MajorUnit = wks.Range("$L$5").Value
        End If
    End Sub
  • Problem with updating only when axis limit cell is changed is if I just want to change the plot and have the axes limit cells update with a formula, that doesn’t count as changing the cell
  • Problem with updating when calculation occurs is… I get errors as that code seems to apply to every worksheet but I don’t want it to apply to every worksheet. I may have to investigate how to fix it as updating when calculation occurs seems ideal.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s