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
0
11/24/2005 4:46:06 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
566 Views

Similar Articles

[PageSpeed] 6

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
 
0
11/25/2005 1:43:45 PM
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" <CaroKannDefence@discussions.microsoft.com> wrote in 
message news:475B1441-E022-415F-9C12-FFD80482B405@microsoft.com...
> 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 


0
11/26/2005 5:54:09 PM
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" <CaroKannDefence@discussions.microsoft.com> wrote in 
> message news:475B1441-E022-415F-9C12-FFD80482B405@microsoft.com...
> > 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 
> 
> 
> 
0
11/29/2005 8:07:06 PM
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" <CaroKannDefence@discussions.microsoft.com> wrote in 
>>message news:475B1441-E022-415F-9C12-FFD80482B405@microsoft.com...
>>
>>>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
0
andy9699 (3616)
11/30/2005 8:51:21 AM
Reply:

Similar Artilces:

Remote data access
As a new .net developer, I would like to know how a VB.net Windows application can access a SQL Server database residing on a web server. In other words, using the Visual Studio IDE, is there a way to develop a Visual Basic, Windows application that can access a SQL Server database over the internet. Thanks for suggestions, John C. John C. wrote: > As a new .net developer, I would like to know how a > VB.net Windows application can access a SQL Server > database residing on a web server. > > In other words, using the Visual Studio IDE, is there a > way to develop...

Data Validation #12
Hi. I have data validations in sheet1 as named range lists from sheet2. When I have deleted sheet2 and copy sheet2 from identical other workbook - my validations does is not work... I see Name ranges in copied worksheet, but when I use they in validation - I have error: "The Source currently evaluates an error. Do you wish continue?" Only after deleting of Named ranges in Sheet2 and recreating its, my validations works. It is possible after replacing of worksheet with named ranges for validations (named ranges names are identical) use copied worksheet without recreating ...

Data Entry Form 03-20-08
Hi, I created a form to use for viewing currently records and entering new data. This time, the form doesn't allow me to enter new data. In fact, all text boxes didn't let me to enter new information. Please help Thanks Chi Several things to check: In form properties: Allow Additions = Yes Allow Edits = Yes If the form's record source is a query, be sure the query is updatable. -- Dave Hargis, Microsoft Access MVP "Chi" wrote: > Hi, > > I created a form to use for viewing currently records and entering new data. > > This time, the ...

VBA
I have created code that inserts lines into a financial statement but need to have certain cells in other code stay constant. B1 in certain code must stay at B1 regardless of changes to the spreadsheet. Any suggestions?? --- Message posted from http://www.ExcelForum.com/ Use the INDIRECT function. E.g., =INDIRECT("B1") The B1 will remain so regardless of inserting and deleting rows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "bforster1 >" <<bforster1.1cmxzp@excelforum-nospam.com> wrote in m...

excel VBA
How would i search a range of a whole column instead of specific cells like .Range("a1:a300")? --- Message posted from http://www.ExcelForum.com/ ..range("a:a") ..columns(1) ..range("a1").entirecolumn are a few ways. "Zygoid <" wrote: > > How would i search a range of a whole column instead of specific cells > like .Range("a1:a300")? > > --- > Message posted from http://www.ExcelForum.com/ -- Dave Peterson ec35720@msn.com ahhh! Thanks!! I was trying .Column(1) i see i neede an "s" in there Thanks a...

Sorting lots of data
We are trying to sort through a csv file that we converted to an excel spreadsheet. the spreadsheet has over 40,000 entries which include mailing addresses that are in some cases duplicated. is there anyway to sort/filter the info to locate the duplicates and then remove them?? any help would be greatly appreciated. thank you for your responses. Hi use 'Data - Advanced Filters' and check 'unique entries' or use the following addin for this: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany miss lynar wrote: > We are trying to sort thr...

Stacking Data??
if I have data like this A B C D... 1 Type\Date 01/10/04 02/10/04 03/10/04... 2 T-X 100 50 100 3 T-Y 50 70 0 4 T-Z 400 0 400 ... .. ...

How do make a line correspond to date
I have a chart with horizontal bars and I want to put a vertical (trend) date line that corresponds to today's date. -- DMM http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "soconfused" <soconfused@discussions.microsoft.com> wrote in message news:ADC1BD32-DF02-4A7F-B074-DCBBA9D0AAFD@microsoft.com... >I have a chart with horizontal bars and I want to put a vertical (trend) >date > line that corresponds to today's...

Label lines disappear when I copy to Powerpoint
Just created a pie chart in Excel 2007 with Labels which have a line joining the label to the pie segment, however when I copy and paste into Powerpoint the lines joining the labels to the segments disappear. Anyone know how to fix this? Need presentation for tomorrow! __________ Information from ESET Smart Security, version of virus signature database 4031 (20090423) __________ The message was checked by ESET Smart Security. http://www.eset.com ...

order data by a rank
i need to create a form order by the militaty rank.This form is based in a query "qrystaff" with three fields: MemberID FullName and Rank.I´d like that the data were ordered firstly by MilitaryRank and secondly by FullName in alphabetical order.There is an easy way to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1 When you create queries, you're given the option to specify the sort order under each field. If the fields aren't in the order from left-to-right in the query grid, you can add ...

Turn data #2
Is there a way to turn the data in an Excel spreadsheet so that what is on the top is now on the side and what is on the side is now on top? I need to export my spreadsheet into Access but, I can't get it to format correctly in its current orientation. ~Make a backup copy of your data to work from (just in case, so you don't lose data) ~Highlight the entire range of data; click >Edit >Copy ~Choose a new location, on a different sheet perhaps; click >Edit >Paste Special and select the Transpose box; click OK Does that do it for you? thank you so much. You are a genius...

Re: Two-Column Problem
After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Charley Kyd <kyd@incsight.com>... > But now, when I add a pair of pages, Publisher gives me guides for two > columns per page but gives me only one page-wide textbox per page, > not two. Can't you create your text boxes yourself? Or change the text box that appears to a two-column one? -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer: http://mvps.org/the_nerd/disclaim.htm ...

Data in columns not rows
I have file that is names, addresses and phone numbers. When I copy it into excel it comes out like this. (COLUMNA) NAME ADDRESS ADDRESS2 PHONE I need it to be like this COLUMNA COLUMNB COLUMNC COLUMND NAME ADDRESS ADDRESS2 PHONE Is there a way to change this without copying and pasting? Jenn Is the data consistently 4 rows? Or do you have varying sets? How far down Column A do the sets extend? If 4 rows per set try this..... In B1 enter =INDEX($A$1:$A$3000,(ROW()-1)*4+COLUMN()-1,1) Drag/copy across to E1 then select B1:E1 and drag/copy down u...

Moving from subform back to mainform
I want to tab from the last record in my subform back over to the open main form without opening a new record on the main form. On Lostfocus I have coded: Me.Parent.Form![Subtotal].SetFocus DoCmd.GoToRecord That takes me to the correct control, but on a new main form - not the current main form. Any help in my coding would be greatly appreciated! Natalie: I know you got the answer, but in case someone else needs it, here's where the answer was given: http://www.access-programmers.co.uk/forums/showthread.php?p=656980#post656980 -- Bob Larson Access World Forums Super Moderator ...

Using a Text / Data output as a cell reference
I am trying to use the end of a column as a divisor and need to convert what i guess is text into an actual cell reference if possible. In column A, say there are 13 data points ending at cell A13. I then try to turn that into a cell reference with the formula ="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1 the output of this is then A13 what i need this to do, is be able to divide any cell by A13. The reason I am going through all this trouble is that there are many columns and each has a different # of data points. I hope this is somewhat clear. Thanks ...

XY Chart
I have created a short VBA macro that reads x and y data from a spreadsheet, where every row represents a new series. Besides the x and y data colums, I also have columns that specify the size of the marker, the markerstyle, and the foreground and background colors. I have adapted some of John Walkenbach's code to label each of the datapoints using .name. The x axis is basically an integer 1 to 5 and the y axis is a value like price or variable margin. There usually are multiple datapoints on any given integer on the x axis. For example, I may be plotting the price that a custom...

Display of CRM data in SharePoint
Is is possible to allow non-CRM users the ability to view CRM data on SharePoint without using the CRM external connector? Thanks, -Rick M. Microsoft would require you to have a license per user I think ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "Rick M" <RickM@discussions.microsoft.com> wrote in message news:B3E731A1-D059-4D49-B879-6025A1817FD8@microsoft.com... > Is is possible to allow non-CRM users the ability to view CRM data on > SharePoint without using the CRM external connector? > > Thanks, > -Rick...

Outlook Data File Not Closing Properly
I always get the following error message when starting Outlook 2002 (running Office XP and Windows XP) : "The data file 'outlook' was not closed properly. The file is being checked for problems." I get this message everytime (whether I hibernate or shut down) when I start Outlook. This checking of the file can take anywhere from 30 seconds to 5 minutes and is very frustrating. Anyone have any idea what the issue is ? This started happening just after I installed XP SP3. ...

adding letters in a payroll calendar
I would like to total all of the "v" for vacation, "s" for sick and "p" for personal days in a excel worksheet. So, if I had a total of 10 "v" in different cells each representing a different day of the month I would like to total those. Is this possible? Hi have a look at COUNTIF and SUMIF -- Regards Frank Kabel Frankfurt, Germany "ryanjh79" <ryanjh79@discussions.microsoft.com> schrieb im Newsbeitrag news:C15AADF1-E56D-49BE-9B05-26C6C2662645@microsoft.com... >I would like to total all of the "v" for vacation, "...

BP Installation Error
Someone post this question before:- MbfPackager data importation process has failed. please c:\DOCUME~1\ADMIN~1\LOCALS~1\Temp\MbfPackager.Import.NoMerge.Common.xml for more information. Problem: When installing Microsoft Business Portal 2.5. Can anyone help me on this error? I'm installing Business Portal 2.5 Feature Pack on Windows 2003 Standard Edition. And, the BP is reading the Great Plains 8.0 database at another server. Cheers, Emily ...

Moving mailboxes to another server
Hi, We are running Exchange 2000 on a Windows 2000 server. I just built a Exchange 2003 on a Windows 2003 server. When I "right mouse" click on the mailboxes in the Exchange system Manager on the Exchange 2000 system, it does not have the option for "exchange tasks" to move the mailboxes to the new exchange 2003 server. It does give me the option in "AD". It's a slower process to move it through AD. Any thoughts to add the feature in the Exchange 2000 system manager? Mitch On Mon, 16 May 2005 09:30:11 -0700, "mitch" <mitch@discussions.micr...

Access data
Is there a way to open an Access 97 database with Excel 97? Hi C, I have a hyperlink in my excel workbook that opens the access workbook, i use it for correction purposes. Would a hyperlink work? Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21287 View this thread: http://www.excelforum.com/showthread.php?threadid=492101 In Excel try Data>Import External Data>Import Data or New Database Query Gord Dibben Excel MVP On Fri, 9 Dec 2005 07:43:...

Importing data from Excel to Outlook
I have an excel spreadsheet with over 2500 contacts. The Spreadsheet is set up with Last Name, First Name in Column A, Full Address in Column B, Email Address in Column C. I have highlighted and defined a name for each of the columns. Each time I attempt to import the data into Outlook 2003 the importing and exporting window never completes its task. I tried to perform an import with a new excel workbook with only limited information and the same thing happens. Any ideas? I've had the same problem. First, save your file as a text file, then import into Outlook. HTH -- Since...

Fixed straight line(s) on a chart
How can I draw a fixed straight line in an excel chart. A line on a level I can easily adjust changing the value in a spreadsheet cell, for example. Is it possible to fix even multiple lines (eg. as a fixed "bandwidth information" - data comparing to a bandwidth)? Thanks Lukas Hi See the following sites: http://andypope.info/charts/averageline.htm http://www.tushar-mehta.com/excel/charts/straight_lines/index.html http://peltiertech.com/Excel/Charts/DummySeries.html#AddLine -- Regards Frank Kabel Frankfurt, Germany "Lukas" <lukas.bieri@gmx.net> schrieb im Newsbeit...

Web query to create historical stock data
Web query is easy to schedule 5 minute refresh, however I want to retain this 5minute data to create a database continuing throughout the trading day to generate 5 minute intraday price/volume data, my question is how can I move the data up or down so as not to remove the previous 5minutes data as the new data is generated. Any suggestions appreciated. thx You will need to (use a macro) move to another sheet. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "ChrisT" <ChrisT@discussions.microsoft.com> wrote in message news:8C721E31-87C5-4C99-8119-F57557BDA5E3@m...