The problem can be summarized like this:
This problem only occurs if I run the code, not when I step through the
chrt.HasTitle
-line. In that case, it correctly creates a title and I can edit it however I want.
I'll first give the relevant source code. This simply includes the code that distinguishes Excel 2003 from newer versions (since Excel 2003 crashes on the 'newer' code and vice versa).
Sub EnableChartTitle(chrt As Chart)
If Application.Version = "11.0" Then
EnableChartTitle_2003 chrt
Else
EnableChartTitle_Post2003 chrt
End If
End Sub
Sub EnableChartTitle_2003(chrt As Chart)
chrt.HasTitle = True
End Sub
Sub EnableChartTitle_Post2003(chrt As Chart)
chrt.SetElement msoElementChartTitleAboveChart
End Sub
The use of chrt.SetElement
previously bypassed this exact issue, but now it seems to be back with Office 2016. Using the old chrt.HasTitle
results in the same issue with 2016.
This code worked perfectly for all versions of Office until Office 2016. Now it suddenly refuses to enable the title unless I step through the code which is of course not the intended use.
Anyone knows what is up with this and a possible fix? VBA really gets on my nerves with this kind of stuff and it's very hard to Google too. This is where I got the previous solution from.
Here is the code that creates the chart:
Dim chrt As Chart
RI.rSheet.Activate
Set chrt = Charts.Add.Location(xlLocationAsObject, RI.rSheet.Name)
Then its location is set, any series that were auto-added are removed and new data is added using chrt.SeriesCollection.Add <range>
.
Notice: The exact same code does work in another script. This is because here, there is only one series added. When setting the name of the series, Excel automatically enables the title. In this script, multiple series are added and as soon as the second set of data is added, the title is automatically removed again. Then it won't enable it again anymore.
I found an answer. You have to tell VBA twice what he has to do before he listens. Changing the code simply to
Sub EnableChartTitle_Post2003(chrt As Chart)
chrt.SetElement msoElementChartTitleAboveChart
chrt.SetElement msoElementChartTitleAboveChart
End Sub
fixed the problem.
This answer was not at all satisfactory...