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

14-080: Fort Bragg Town Hall

2014-07-11

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

Better late than never with this update…

2014-06-27

  • Wrote report.

2014-06-26

  • Worked on plot, figures

2014-06-25

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

2014-06-24

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

2014-06-23

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

2014-06-20

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

2014-06-17

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

2014-06-16

  • Site visit.  Measured data at 3 rooms.

2014-06-13

  • Received project description.  Packed up gear.