Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select different data label range (as values)
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).Ha sDataLabel = True ActiveChart.SeriesCollection(8).Points(Counter).Da taLabel.Text = Range(xVals).Cells(Counter, 1).Offset(0, -9).Value Next Counter End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select different data label range (as values)
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) "MikeM" wrote 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- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select different data label range (as values)
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 "Jim Cone" wrote: 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) "MikeM" wrote 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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Missing Data Label on XY Values | Excel Worksheet Functions | |||
How to select a range based on values in 1 column | Excel Programming | |||
Select range -- convert formulas to values | Excel Programming | |||
using cell values to select range of cells | Excel Programming | |||
How to select range of values in pivot table? | Excel Programming |