how to format excel chart data labels using vba

[bookouri]

Im dont usually use Excel, but Im working on a project where I have to create some charts and configure the chart components using vba. Ive had pretty good luck recording macros and then using the code, but when i record some things like data labels alignment and format and text attributes the macro recorder does not capture those settings. Is there some way to record these settings so I can add them to my code? or point me to a good reference for the chart object? I tried looking for a reference with the settings, but havnt had any luck finding anything useful.

thanks for any help

 

[SkipVought]

Hi,

are you actually changing those data label properties as you record? You have to actually CHANGE the properties, not just open the dialogs.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[N1GHTEYES]

I don't know if it will help, but here is a quick example of how to address and change the labels on a chosen series.

It assumes you are working with the active chart, you have chosen a particular series and know its number, designated by "n". It also assumes you have created an array of strings ("MyDataLabels()") you want to use as the labels of your chosen series, and that the size of the string array is at least as big as the number of points in the series.

'To address the series:
Dim selectedseries As Series
set selectedseries = = ActiveChart.SeriesCollection(n)


'To apply labels:
selectedseries.ApplyDataLabels

'To address the labels:
for i = 1 to selectedseries.Points.Count
  selectedseries.Points(i).DataLabel.Text = MyDataLabels(i)
next i

I hope that helps.

Tony

 

[bookouri]

thanks ill try that code.. but yes during the macro record i was actually changing the font size and alignment and it appeared on the chart, but in the macro code there was just nothing but the chart select code..

 

[N1GHTEYES]

Oops - If you do cut'n'paste the code I posted, please note that I got over-enthusiastic with the "=". The line:

set selectedseries = = ActiveChart.SeriesCollection

should read:

set selectedseries = ActiveChart.SeriesCollection

Tony