A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Charts and Charting in Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Moving data labels around with VBA (and adding a line between two



 
 
Thread Tools Display Modes
  #1  
Old November 24th 05, 04:46 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Moving data labels around with VBA (and adding a line between two

Hello.

I am looking for a way to manage data labels in an automated charting
procedure I am writing.

The chart is a waterfall chart (I believe) and I'd like to be able to loop
through each dataseries/column to find the height of the total column and
then set the label for the total column to sit x points above it.

There is a second issue I need help with. As the chart displays components
of return for stocks and bonds, I'd like to place a line in between the last
stock and the first bond shown. Is there a way that I can place this line
dynamically (charts will like have different numbers of stocks and bonds - as
will a particular chart over time).

I think there is an add-in someone mentioned I could try downloading but I'd
like to avoid that if possible.

Thanks,
Caro-Kann Defence
Ads
  #2  
Old November 25th 05, 01:43 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Moving data labels around with VBA (and adding a line between two


Good day,

I think what you are looking for is what I use to control the chart
from Access when I export data. Change the source data, for series 1
to what ever to something similar to your legend as it is easier to
work with. Running a macro would give you something similar to my code
and there is a few options of where to place the lable.

I have had a little problem to display both the legend or
identification line and the value in the same block and that is why
there is two sections and it is display as a line chart.

With Source data you can change the series to a name.

With ActiveChart
With ActiveChart.Axes(xlValue)
.MinimumScale = 30
.MaximumScale = 170
.MinorUnit = 1
' .MajorUnitIsAuto = True
'.Crosses = xlCustom
.CrossesAt = 30
.ReversePlotOrder = False
'.ScaleType = xlLinear

End With

With .SeriesCollection("Upper Control Limit").Points(19)
.HasDataLabel = True
.DataLabel.Text = " Upper Control Limit ="
.DataLabel.Font.Size = 7
.DataLabel.Font.Italic = True
.DataLabel.HorizontalAlignment = xlRight
.DataLabel.VerticalAlignment = xlTop
.DataLabel.Position = xlLabelPositionAbove
.DataLabel.Orientation = xlHorizontal
End With

With .SeriesCollection("Upper Control Limit").Points(24)
.HasDataLabel = True
' .DataLabel.Text = "Upper Control Limit = 2.3"
.DataLabel.Font.Size = 7
.DataLabel.Font.Italic = True
.DataLabel.HorizontalAlignment = xlRight
.DataLabel.VerticalAlignment = xlTop
.DataLabel.Position = xlLabelPositionAbove
.DataLabel.Orientation = xlHorizontal
End With


I hope this helps

Hennie
Caro-Kann Defence wrote:
> *Hello.
>
> I am looking for a way to manage data labels in an automated
> charting
> procedure I am writing.
>
> The chart is a waterfall chart (I believe) and I'd like to be able to
> loop
> through each dataseries/column to find the height of the total column
> and
> then set the label for the total column to sit x points above it.
>
> There is a second issue I need help with. As the chart displays
> components
> of return for stocks and bonds, I'd like to place a line in between
> the last
> stock and the first bond shown. Is there a way that I can place this
> line
> dynamically (charts will like have different numbers of stocks and
> bonds - as
> will a particular chart over time).
>
> I think there is an add-in someone mentioned I could try downloading
> but I'd
> like to avoid that if possible.
>
> Thanks,
> Caro-Kann Defence *




--
hennie
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message1990730.html

  #3  
Old November 26th 05, 05:54 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Moving data labels around with VBA (and adding a line between two

I follow a methodology which is a little more complicated in the chart, but
much less so in VBA (in fact, it requires NO VBA).

For each set of labels I need, I add a line chart (or XY chart) series to
the chart, with X and Y values selected to precisely locate the label with
respect to the floating columns. Various formulas adjust these X and Y as
needed to move the labels to align with the columns. The added series are
formatted to be hidden (no line, no markers). Then I use one of these free
Excel add-ins to apply the appropriate datalabels to the points. Once
applied, I never need to readjust them, because the datalabel text is linked
to the cells in the worksheet that contain the text for the labels.

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com/ss

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Caro-Kann Defence" > wrote in
message ...
> Hello.
>
> I am looking for a way to manage data labels in an automated charting
> procedure I am writing.
>
> The chart is a waterfall chart (I believe) and I'd like to be able to loop
> through each dataseries/column to find the height of the total column and
> then set the label for the total column to sit x points above it.
>
> There is a second issue I need help with. As the chart displays components
> of return for stocks and bonds, I'd like to place a line in between the
> last
> stock and the first bond shown. Is there a way that I can place this line
> dynamically (charts will like have different numbers of stocks and bonds -
> as
> will a particular chart over time).
>
> I think there is an add-in someone mentioned I could try downloading but
> I'd
> like to avoid that if possible.
>
> Thanks,
> Caro-Kann Defence



  #4  
Old November 29th 05, 08:07 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Moving data labels around with VBA (and adding a line between

Hi Jon.

Thanks for the response. I do have one follow-up question: is there a way to
set the label values to show different values than the ones that would
normally be automatically picked up in the chart? For example, instead of
showing a label value of "2" could I tie it to a cell in the worksheet that
has a value "4" (or "Eggs" for that matter)?

Thanks again. I am a little concerned about usign the Add-ins given my IT
department may be a tough group to allow external programs being brought in
(sorry!).

CKD

"Jon Peltier" wrote:

> I follow a methodology which is a little more complicated in the chart, but
> much less so in VBA (in fact, it requires NO VBA).
>
> For each set of labels I need, I add a line chart (or XY chart) series to
> the chart, with X and Y values selected to precisely locate the label with
> respect to the floating columns. Various formulas adjust these X and Y as
> needed to move the labels to align with the columns. The added series are
> formatted to be hidden (no line, no markers). Then I use one of these free
> Excel add-ins to apply the appropriate datalabels to the points. Once
> applied, I never need to readjust them, because the datalabel text is linked
> to the cells in the worksheet that contain the text for the labels.
>
> Rob Bovey's Chart Labeler, http://appspro.com
> John Walkenbach's Chart Tools, http://j-walk.com/ss
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> "Caro-Kann Defence" > wrote in
> message ...
> > Hello.
> >
> > I am looking for a way to manage data labels in an automated charting
> > procedure I am writing.
> >
> > The chart is a waterfall chart (I believe) and I'd like to be able to loop
> > through each dataseries/column to find the height of the total column and
> > then set the label for the total column to sit x points above it.
> >
> > There is a second issue I need help with. As the chart displays components
> > of return for stocks and bonds, I'd like to place a line in between the
> > last
> > stock and the first bond shown. Is there a way that I can place this line
> > dynamically (charts will like have different numbers of stocks and bonds -
> > as
> > will a particular chart over time).
> >
> > I think there is an add-in someone mentioned I could try downloading but
> > I'd
> > like to avoid that if possible.
> >
> > Thanks,
> > Caro-Kann Defence

>
>
>

  #5  
Old November 30th 05, 08:51 AM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Moving data labels around with VBA (and adding a line between

Hi,

You can link a datalabel, as well as chart titles, to cells. Here is an
explanation of how to do it manually for the chart title but the
principle is the same for data labels. Just click the data labels once
to select and the click the individual data label in order to be able to
link to a cell.
http://www.andypope.info/tips/tip001.htm

Cheers
Andy

Caro-Kann Defence wrote:
> Hi Jon.
>
> Thanks for the response. I do have one follow-up question: is there a way to
> set the label values to show different values than the ones that would
> normally be automatically picked up in the chart? For example, instead of
> showing a label value of "2" could I tie it to a cell in the worksheet that
> has a value "4" (or "Eggs" for that matter)?
>
> Thanks again. I am a little concerned about usign the Add-ins given my IT
> department may be a tough group to allow external programs being brought in
> (sorry!).
>
> CKD
>
> "Jon Peltier" wrote:
>
>
>>I follow a methodology which is a little more complicated in the chart, but
>>much less so in VBA (in fact, it requires NO VBA).
>>
>>For each set of labels I need, I add a line chart (or XY chart) series to
>>the chart, with X and Y values selected to precisely locate the label with
>>respect to the floating columns. Various formulas adjust these X and Y as
>>needed to move the labels to align with the columns. The added series are
>>formatted to be hidden (no line, no markers). Then I use one of these free
>>Excel add-ins to apply the appropriate datalabels to the points. Once
>>applied, I never need to readjust them, because the datalabel text is linked
>>to the cells in the worksheet that contain the text for the labels.
>>
>> Rob Bovey's Chart Labeler, http://appspro.com
>> John Walkenbach's Chart Tools, http://j-walk.com/ss
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>"Caro-Kann Defence" > wrote in
>>message ...
>>
>>>Hello.
>>>
>>>I am looking for a way to manage data labels in an automated charting
>>>procedure I am writing.
>>>
>>>The chart is a waterfall chart (I believe) and I'd like to be able to loop
>>>through each dataseries/column to find the height of the total column and
>>>then set the label for the total column to sit x points above it.
>>>
>>>There is a second issue I need help with. As the chart displays components
>>>of return for stocks and bonds, I'd like to place a line in between the
>>>last
>>>stock and the first bond shown. Is there a way that I can place this line
>>>dynamically (charts will like have different numbers of stocks and bonds -
>>>as
>>>will a particular chart over time).
>>>
>>>I think there is an add-in someone mentioned I could try downloading but
>>>I'd
>>>like to avoid that if possible.
>>>
>>>Thanks,
>>>Caro-Kann Defence

>>
>>
>>


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.