CODEWITHSUNDEEP
excelvba
Capt.Krusty
Capt.Krusty

Reputation: 627

Unwanted chart changes over time in Excel

Intro:

I am having a chart ("myChart") in Excel which has two dataseries within.
Via Formula > Name Manager both dataseries correspond to defined data columns within the sheet. Those columns change their length via input data (>snippet 1) and therefore the chart shall dynamically change its form. After giving the input for the column changes I manually run the CustomLabels() macro for changing the labeling of my maxima values ("Batch").
The display of the chart happens without problems and changes accordingly (first picture).

Snippet 1 =OFFSET(Book1.xlsm!Name1;0;0;COUNTA(Sheet1!$G:$G)-1;1)

myChart myChart - wanted outcome

Labelling Macro

Sub CustomLabels()

   Dim i, myCount, pt
   ActiveSheet.ChartObjects("myChart").Activate
   myCount = ActiveChart.SeriesCollection(1).Points.Count

   For i = 2 To myCount
       ActiveChart.SeriesCollection(1).Points(i).ApplyDataLabels
       ActiveChart.SeriesCollection(1).Points(i).DataLabel.Text = Range("I" & i + 6).Value
   Next i

   ActiveSheet.ChartObjects("myChart").Activate
   myCount = ActiveChart.SeriesCollection(2).Points.Count

   For i = 2 To myCount
       ActiveChart.SeriesCollection(2).Points(i).ApplyDataLabels
       ActiveChart.SeriesCollection(2).Points(i).DataLabel.Text = Range("O" & i + 6).Value
   Next i

End Sub

Problems:

1) Axis

Although my axis labes column is in custom format DD.MM.YYYY hh:mm, the axis datalabels look as displayed in the picture above.
Is this a format error or what do I have to do?

2) Unwanted chart change

After working wihtin the workbook/sheet and changing values (non-dependent on the chart or vice-versa), the chart changes and looks like in the picture below. Selecting a cell containing chart-data and pressing enter (no changes needed) the chart changes back to the above picture.
What can be the reasons for that?

myChart after saving/working within the sheet myChart - unwanted change

Upvotes: 0

Views: 73

Answers (1)

Алексей Р
Алексей Р

Reputation: 7627

Try this code:

Option Explicit

Sub CustomLabels()

    Dim j, i, cols
    Dim my_chart As Chart, sc As Series
    
    Set my_chart = ActiveSheet.ChartObjects("myChart").Chart
    
    With my_chart.Axes(xlCategory)   ' axis adjustment
        .CategoryType = xlCategoryScale ' not XlCategoryType.xlAutomaticScale | XlCategoryType.xlTimeScale
        .TickLabels.NumberFormat = "DD.MM.YYYY hh:mm"
        .TickLabels.Orientation = xlUpward
    End With
    
    cols = Array("I", "O")  'columns containing labels
    For j = 1 To my_chart.SeriesCollection.Count
        Set sc = my_chart.SeriesCollection(j)
    
        For i = 2 To sc.Points.Count
            sc.Points(i).ApplyDataLabels
            sc.Points(i).DataLabel.Text = Range(cols(j - 1) & i + 6).Value
        Next i
    Next j
End Sub

enter image description here

Upvotes: 1

Related Questions