Charts & Macros: how to shift set of data labels & marker dots periodically

[sthlm]

Hello!

There is a graph that I need to update on a monthly basis which contains:- bars showing the value (left vertical axis) for each month for last 4 years
- line with the percentages (right vertical axis)
- months for the last 4 years (horizontal axis)


It has data labels on the bars and marker dots on the line. If we are in October, I will have data labels and marker dots for October for 2011, 2012, 2013 and 2014. In November I have to delete all the labels and dots and create new ones for Novembers.


Is there a macro that I can use instead of doing this manually every month? I am using Excel 2010.

 

[Jon Peltier]

Easy enough to cobble something together. I assume the label text is the same as the X axis label for the labeled point.

Sub LabelOnceAYearThisMonth()
  Dim srs As Series
  Dim vXvals As Variant
  Dim iPt As Long
  
  Set srs = ActiveChart.SeriesCollection(1)
  vXvals = srs.XValues
  For iPt = srs.Points.Count To 1 Step -12
    ' step -12 goes backwards 12 months at a time
    With srs.Points(iPt)
      .HasDataLabel = True
      .DataLabel.Text = vXvals(iPt)
    End With
  Next
End Sub

 

[Jon Peltier]

Oops, gotta delete the old labels:

Sub LabelOnceAYearThisMonth()
  Dim srs As Series
  Dim vXvals As Variant
  Dim iPt As Long
  
  Set srs = ActiveChart.SeriesCollection(1)
  srs.HasDataLabels = False
  vXvals = srs.XValues
  For iPt = srs.Points.Count To 1 Step -12
    ' step -12 goes backwards 12 months at a time
    With srs.Points(iPt)
      .HasDataLabel = True
      .DataLabel.Text = vXvals(iPt)
    End With
  Next
End Sub

 

[sthlm]

Thanks Jon, this almost worked.
When I try your code, it deletes old labels and creates new ones for the next month, but it is using month labels (2010-11, 2011-11,..) that I have on the X axis instead of the values that I need from the left (primary) Y axis. Do you know how can this be fixed? My VBA knowledge is very very basic, so I can't catch the mistake in the code.

Is there a way to move in the same way also the dots (black) that I have on the line on the same graph (taking the values from the right (secondary) Y axis?

 

[Jon Peltier]

You have dates on a vertical axis? Unconventional, and probably confusing. But it means you want the Values instead of XValues. Also, I didn't get the bit about markers. I'll assume the desired marker type is a circle.

Sub LabelOnceAYearThisMonth()
  Dim srs As Series
  Dim vVals As Variant
  Dim iPt As Long
  
  Set srs = ActiveChart.SeriesCollection(1)
  srs.HasDataLabels = False
  srs.MarkerStyle = xlMarkerStyleNone
  vVals = srs.Values
  For iPt = srs.Points.Count To 1 Step -12
    ' step -12 goes backwards 12 months at a time
    With srs.Points(iPt)
      .HasDataLabel = True
      .DataLabel.Text = vVals(iPt)
      .MarkerStyle = xlMarkerStyleCircle
    End With
  Next
End Sub