Local settings change in pop-up label in a chart. Excel, VBA

Disclaimer: Since you are using English language for this post, I will respond in the language you are using. As this is the Dutch forum, you are welcome to let me know if you need me to respond in Dutch. I respond in the language you are comfortable with. 

Hello Eddy Vlaemynck

Thank you for providing the screenshots and detail desciption about this issue.

They clarify that the date labels on the horizontal axis remain correct, but after the macro updates the series, the chart’s hover tooltip displays 30-08-yy instead of the expected date.

The following line only formats the labels displayed on the category axis:

.Axes(xlCategory).TickLabels.NumberFormatLocal = "dd-mm-jjjj"
``

It does not directly control the built-in tooltip that appears when the pointer is placed over a chart point. Excel VBA provides a setting to enable or disable tooltips, but Microsoft does not document a separate number-format property for the built-in chart tooltip.

The yy shown literally suggests that the localized format code is being interpreted in a different language context. Try formatting the source date cells using the language-independent NumberFormat property and yyyy, rather than NumberFormatLocal and the Dutch jjjj code:

Sub Water()
 Dim wmax As Long
 Dim lbls As Range
 Dim vals As Range
 Dim ser As Series
 wmax = 16
 Set lbls = Sheets(1).Range("A1:A" & wmax)
 Set vals = Sheets(1).Range("B1:B" & wmax)
 Set ser = Sheets(2).ChartObjects(1).Chart.SeriesCollection(1)
 lbls.NumberFormat = "dd-mm-yyyy"
 With ser
 .Name = "Water"
 .XValues = lbls
 .Values = vals
 End With
 With Sheets(2).ChartObjects(1).Chart
 .Axes(xlCategory).TickLabels.NumberFormat = "dd-mm-yyyy"
 .Refresh
 End With
End Sub

This also avoids constructing the SERIES formula as text. Microsoft documents that both Series.XValues and Series.Values can be assigned directly from worksheet ranges.

Refer here for documentation about this: Series.XValues documentation and Series.Values documentation

Please test the macro on a copy of the workbook. If the tooltip still shows yy literally while the source cells and axis labels remain correct, that will indicate a limitation or unexpected behavior in Excel’s built-in chart tooltip rather than the category-axis formatting.

In that case, a fully controlled tooltip would require a custom VBA solution, such as displaying formatted text in a text box when a chart point is selected.

I hope this helps.

Feel free reply back to this post if you need further assistance, I'll be happy to help.

Het kan inderdaad in het Nederlands. Maar de help verwijst naar de US-Site. Ik dus alles maar in het Engels doorgegeven...

Uiteindelijk nemen ze niks aan en kan ik niks plaatsen... Dan maar een andere weg gezocht (deze weg) maar niet de moeite genomen om alles terug in het Nederlands te zetten... Ondertussen zelf een oplossing gevonden...

Als ik de code uitvoer zoals U me hebt gegeven:

Sub Water()
 Dim wmax As Long
 Dim lbls As Range
 Dim vals As Range
 Dim ser As Series
 wmax = 16
 Set lbls = Sheets(1).Range("A1:A" & wmax)
 Set vals = Sheets(1).Range("B1:B" & wmax)
 Set ser = Sheets(2).ChartObjects(1).Chart.SeriesCollection(1)
 lbls.NumberFormat = "dd-mm-yyyy"
 With ser
 .Name = "Water"
 .XValues = lbls
 .Values = vals
 End With
 With Sheets(2).ChartObjects(1).Chart
 .Axes(xlCategory).TickLabels.NumberFormat = "dd-mm-yyyy"
 .Refresh
 End With
End Sub

Krijg ik dit:

Dat is eigenlijk niet beter. Maar ik heb wel opgemerkt dat als je kolom A terug een "format" geeft dat 30-08-yyyy terug 30-8-2026 geeft, en bij kolom B hetzelfde, waarde komt terug goed... en ik heb dit nu vanuit het programma gedaan...

Sub Water()
 Dim wmax As Long
 Dim lbls As Range
 Dim vals As Range
 Dim ser As Series
 
 wmax = 16
 Set lbls = Sheets(1).Range("A1:A" & wmax)
 Set vals = Sheets(1).Range("B1:B" & wmax)
 Set ser = Sheets(2).ChartObjects(1).Chart.SeriesCollection(1)
 lbls.NumberFormat = "dd-mm-yyyy"
 
 With ser
    .Name = "Water"
    .XValues = lbls
    .Values = vals
 End With
 
 With Sheets(2).ChartObjects(1).Chart
    .Axes(xlCategory).TickLabels.NumberFormat = "dd-mm-yyyy"
    .Refresh
 End With

 Sheets(1).Columns("A").NumberFormat = "dd-mm-yyyy"
 Sheets(1).Columns("B").NumberFormat = "0.000"
End Sub

En dan krijg ik dit:

Dat is OK voor mij... (nu programma voor digitale meter bijwerken...)