Postioning of labels on charts using vba
 

I have an excel sheet that is populated from a database. The excel tables
that are populated act as data ranges for a chart. Now especially on a pie
chart we would like to see the data labels on each slice. However, not
knowing the values in advance leads to manual repositioning of the labels
especially for small values which often have to be placed outside the pie.

Can label positioning be done programatically per slice (or bar in a bar
chart) according to the value.

Thank you
--
Dean Mann

Postioning of labels on charts using vba
 

Hello,

this might not be exactly what you want but maybe it helps on the way ...

Dim c As ChartObject

' Set a reference to your chart object ...
Set c = ActiveSheet.ChartObjects("Diagramm 1")
' Turn on data labels (depends on whether the already are visible or not)
....
c.Chart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False,
ShowCategoryName:= _
False, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False
' In a pie chart there is only one serie of data, so it is
SeriesCollection(1)
' In my example I set the label coordinates of label for point 2
c.Chart.SeriesCollection(1).Points(2).DataLabel.Le ft = 50
c.Chart.SeriesCollection(1).Points(2).DataLabel.To p = 200


Herbert


"Dean" wrote:

I have an excel sheet that is populated from a database. The excel tables
that are populated act as data ranges for a chart. Now especially on a pie
chart we would like to see the data labels on each slice. However, not
knowing the values in advance leads to manual repositioning of the labels
especially for small values which often have to be placed outside the pie.

Can label positioning be done programatically per slice (or bar in a bar
chart) according to the value.

Thank you
--
Dean Mann

Postioning of labels on charts using vba
 

Thank you for that and useful. Is the .Left and .Top measurements relative to
the pie slice or to the chart as a whole?

Regards
Dean
--
Dean Mann


"Herbert" wrote:

Hello,

this might not be exactly what you want but maybe it helps on the way ...

Dim c As ChartObject

' Set a reference to your chart object ...
Set c = ActiveSheet.ChartObjects("Diagramm 1")
' Turn on data labels (depends on whether the already are visible or not)
...
c.Chart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False,
ShowCategoryName:= _
False, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False
' In a pie chart there is only one serie of data, so it is
SeriesCollection(1)
' In my example I set the label coordinates of label for point 2
c.Chart.SeriesCollection(1).Points(2).DataLabel.Le ft = 50
c.Chart.SeriesCollection(1).Points(2).DataLabel.To p = 200


Herbert


"Dean" wrote:

I have an excel sheet that is populated from a database. The excel tables
that are populated act as data ranges for a chart. Now especially on a pie
chart we would like to see the data labels on each slice. However, not
knowing the values in advance leads to manual repositioning of the labels
especially for small values which often have to be placed outside the pie.

Can label positioning be done programatically per slice (or bar in a bar
chart) according to the value.

Thank you
--
Dean Mann

Postioning of labels on charts using vba
 

To the chart, as far as I know

Herbert

"Dean" wrote:

Thank you for that and useful. Is the .Left and .Top measurements relative to
the pie slice or to the chart as a whole?

Regards
Dean
--
Dean Mann


"Herbert" wrote:

Hello,

this might not be exactly what you want but maybe it helps on the way ...

Dim c As ChartObject

' Set a reference to your chart object ...
Set c = ActiveSheet.ChartObjects("Diagramm 1")
' Turn on data labels (depends on whether the already are visible or not)
...
c.Chart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False,
ShowCategoryName:= _
False, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False
' In a pie chart there is only one serie of data, so it is
SeriesCollection(1)
' In my example I set the label coordinates of label for point 2
c.Chart.SeriesCollection(1).Points(2).DataLabel.Le ft = 50
c.Chart.SeriesCollection(1).Points(2).DataLabel.To p = 200


Herbert


"Dean" wrote:

I have an excel sheet that is populated from a database. The excel tables
that are populated act as data ranges for a chart. Now especially on a pie
chart we would like to see the data labels on each slice. However, not
knowing the values in advance leads to manual repositioning of the labels
especially for small values which often have to be placed outside the pie.

Can label positioning be done programatically per slice (or bar in a bar
chart) according to the value.

Thank you
--
Dean Mann