Excel: automatically update chart axes and other properties with VBA

http://peltiertech.com/link-excel-chart-axis-scale-to-values-in-cells/

http://peltiertech.com/calculate-nice-axis-scales-in-your-excel-worksheet/

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