Select different data label range (as values)

[MikeM]

Hello All:

Below is the code from Microsoft to apply category labels to an XY data
series.
I've changed the SeriesCollection and Offset values to fit my chart and it
works fine.
However, the data labels are text labels and I really need them to be values
that can be formatted like any other number value.
Is there any way to modify this code to create the new data labels as values
instead of text?

I've been fooling around with 'DataLabel.Text' in the second to last line to
no avail...

Thanks.

Mike

Sub AttachLabelsToPoints()

'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String

'Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False

'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(8).Formula

'Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, Mid(Left(xVals,
InStr(xVals, "!") - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
Loop

'Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(8).Points(Counter).HasDataLabel = True
ActiveChart.SeriesCollection(8).Points(Counter).DataLabel.Text =
Range(xVals).Cells(Counter, 1).Offset(0, -9).Value
Next Counter

End Sub

 

[Jim Cone]

Also from MS Excel help...

With Charts(1).SeriesCollection(1)
.HasDataLabels = True
.DataLabels.NumberFormat = "##.##"
End With
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




in message
Hello All:
Below is the code from Microsoft to apply category labels to an XY data
series.
I've changed the SeriesCollection and Offset values to fit my chart and it
works fine.
However, the data labels are text labels and I really need them to be values
that can be formatted like any other number value.
Is there any way to modify this code to create the new data labels as values
instead of text?
I've been fooling around with 'DataLabel.Text' in the second to last line to
no avail...
Thanks.
Mike
-snip-

 

[MikeM]

Jim:
Thanks for your reply.

I did scour the MS VB help file and did attempt to plug in
'DataLabels.NumberFormat' into the existing code six ways from Sunday. The
problem is whichever way I put it in there (without generating an error), it
bypassed the substitution of the original series' data label range with the
new series range and simply placed value formatted data labels on the chart
from the original series.

This is a 'waterfall chart' wherein the actual data labels do not reflect
what is happening on the chart (e.g., a 'negative' value above the X axis is
actually charted as a positive value).

I have a separate Line series which corresponds with the total values for
each stacked column series. The data labels of that series are used for the
'floating' parts of the waterfall stacked column series.

The manual way of doing this is to select each data label in turn and link
it to the original data entered by the user (positive values go up, negative
values go down).

I thought I might adapt MS's VBA code for attaching labels to an XY series
as text labels for this other purpose (changing data label range after the
fact). I know that there are add-ins out there that do this quite nicely
(John Walkenbach's Chart Tool, for example.) I was hoping I might be able to
integrate the same functionality into my macro.

There are also Waterfall chart addins as well (Jon Peltier). But, I was
hoping to come up with a reasonable facsimile.

So, my problem is that I can't seem to plug that variable into this code and
get it to work.


Mike