VBA Automatic Labelling

[MicoL]

Hi all,


I am constructing a panel chart, that is, a chart that has multiple regions which compare similar data sets side by side (in separate panels) rather than right on top of each other.
Essentially a single chart is repeated across a grid, with different data sets in each instance of the chart.


Specifically, I am following the instructions available on the website https://peltiertech.com/Excel/ChartsHowTo/PanelUnevenScales.html trying to generalize them to the case of 4 panels instead of the 3 to which the instructions refer.


According to the example reported on the website, the following vba code should be run in order to automatically add value labels to the vertical axes.

[CENTER]
[/CENTER]
Sub AttachLabelsToPoints()

   'Dimension variables.
   Dim Counter As Integer, ChartName As String, xVals As String, xVals1 As String, xVals2 As String

   ' Disable screen updating while the subroutine is run.
   Application.ScreenUpdating = True

   'Store the formula for the first series in "xVals".
   xVals = ActiveChart.SeriesCollection(6).Formula

   'Extract the range for the data from xVals.
   xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
      Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
   xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
   Do While Left(xVals, 1) = ","
      xVals = Mid(xVals, 2)
   Loop

   'Attach a label to each data point in the chart.
   For Counter = 1 To Range(xVals).Cells.Count
     ActiveChart.SeriesCollection(6).Points(Counter).HasDataLabel = _
         True
      ActiveChart.SeriesCollection(6).Points(Counter).DataLabel.Text = _
         Range(xVals).Cells(Counter, 1).Offset(0, 4).Text
   ActiveChart.SeriesCollection(6).Points(Counter).DataLabel.Position = xlLabelPositionLeft
   Next Counter



   'Store the formula for the first series in "xVals".
   xVals1 = ActiveChart.SeriesCollection(5).Formula

   'Extract the range for the data from xVals.
   xVals1 = Mid(xVals1, InStr(InStr(xVals1, ","), xVals1, _
      Mid(Left(xVals1, InStr(xVals1, "!") - 1), 9)))
   xVals1 = Left(xVals1, InStr(InStr(xVals1, "!"), xVals1, ",") - 1)
   Do While Left(xVals1, 1) = ","
      xVals = Mid(xVals1, 2)
   Loop

   'Attach a label to each data point in the chart.
   For Counter = 1 To Range(xVals1).Cells.Count
     ActiveChart.SeriesCollection(5).Points(Counter).HasDataLabel = _
         True
      ActiveChart.SeriesCollection(5).Points(Counter).DataLabel.Text = _
         Range(xVals1).Cells(Counter, 1).Offset(0, 2).Text
   Next Counter



   'Store the formula for the first series in "xVals".
   xVals2 = ActiveChart.SeriesCollection(4).Formula

   'Extract the range for the data from xVals.
   xVals2 = Mid(xVals2, InStr(InStr(xVals2, ","), xVals2, _
      Mid(Left(xVals2, InStr(xVals2, "!") - 1), 9)))
   xVals2 = Left(xVals2, InStr(InStr(xVals, "!"), xVals2, ",") - 1)
   Do While Left(xVals2, 1) = ","
      xVals2 = Mid(xVals2, 2)
   Loop

   'Attach a label to each data point in the chart.
   For Counter = 1 To Range(xVals2).Cells.Count
     ActiveChart.SeriesCollection(4).Points(Counter).HasDataLabel = _
         True
      ActiveChart.SeriesCollection(4).Points(Counter).DataLabel.Text = _
         Range(xVals2).Cells(Counter, 1).Offset(0, 2).Text
   ActiveChart.SeriesCollection(4).Points(Counter).DataLabel.Position = xlLabelPositionLeft
   Next Counter

End Sub

How can I generalize the code to the case of 4 panels ?
Please consider that my file consists of two sheet: "Sheet-1" with the data of interest that I would like to plot and "Sheet-2" with the chart.
What I would like to do is to run the code for the aumatic labelling from a button placed on Sheet-1.