MWE
11-05-2011, 03:46 PM
I am running Excel 2003. I have an XY scatter plot where Y1 and Y2 are plotted against X. I am having difficulty labeling either of the Y series.
When I am not sure how to do something in VBA, I normally turn on the Macro recorder, do what I want manually and then tweak the macro code.
So, I turned on the macro recorder, selected one of the Y's, entered the name I wanted surrounded by double quotes in the =SERIES( ... at the top of the current window and it worked fine and the Legend box was updated. The key line in the macro is
ActiveChart.SeriesCollection(1).Formula = "Desired Name" That did not make much sense, but I tried it anyway. When I inserted that line into my code, the Chart Legend info did not change, it still indicated "Series 1". Rather the text "Desired Name" appeared in a text box in the middle of the chart. I fiddled around with this and could not get it to work.
So I tried a different approach. I turned on the Macro Recorder, selected the chart; in the Chart drop down menu, I selected Source Data and then the Series Tab. In the Name box for Series 1, I entered ="Desired Name", ... Again, this did what I wanted and the Chart Legend info was updated correctly. I then looked at the code the macro recorder had for this approach and the key line was ActiveChart.SeriesCollection(1).Name = "=""DesiredName""" When inserted into my code, that failed with an error. When I poked around the Object Browser, I discovered that SeriestCollection does not have a child called Name.
Both methods work manually, but neither worked programatically. So, how do I label a data series using VBA?
When I am not sure how to do something in VBA, I normally turn on the Macro recorder, do what I want manually and then tweak the macro code.
So, I turned on the macro recorder, selected one of the Y's, entered the name I wanted surrounded by double quotes in the =SERIES( ... at the top of the current window and it worked fine and the Legend box was updated. The key line in the macro is
ActiveChart.SeriesCollection(1).Formula = "Desired Name" That did not make much sense, but I tried it anyway. When I inserted that line into my code, the Chart Legend info did not change, it still indicated "Series 1". Rather the text "Desired Name" appeared in a text box in the middle of the chart. I fiddled around with this and could not get it to work.
So I tried a different approach. I turned on the Macro Recorder, selected the chart; in the Chart drop down menu, I selected Source Data and then the Series Tab. In the Name box for Series 1, I entered ="Desired Name", ... Again, this did what I wanted and the Chart Legend info was updated correctly. I then looked at the code the macro recorder had for this approach and the key line was ActiveChart.SeriesCollection(1).Name = "=""DesiredName""" When inserted into my code, that failed with an error. When I poked around the Object Browser, I discovered that SeriestCollection does not have a child called Name.
Both methods work manually, but neither worked programatically. So, how do I label a data series using VBA?