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.

14-080: Fort Bragg Town Hall


14-065 1480 64th St Epic Care Oncology Radiation Center

Better late than never with this update…


  • Wrote report.


  • Worked on plot, figures


  • Did displacement analysis after getting JTN_MATH to work.  I still need to update my Excel template.


  • Took more data in the morning.
  • Then analyzed data.
  • JTN also went over how to get displacement from acceleration using his JTN_MATH.
  • I had trouble getting this to work on my computer.
  • I tried to get it to work, but gave up and ultimately went to go work on Envy stuff.


  • Finished analyzing acceleration.
  • Data seemed low.
  • JTN wanted to go back out there to get another freight train.  I am going tomorrow.


  • Dealing with fatigue issues last couple days, so didn’t get much work done.
  • Continued analyzing acceleration data.


  • Analyzed data.  Data seemed a little low.  I had issues with getting lxcrop to work.


  • Site visit.  Measured data at 3 rooms.


  • Received project description.  Packed up gear.