A Microsoft Office (Excel, Word) forum. OfficeFrustration

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 » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Line chart, value labeling



 
 
Thread Tools Display Modes
  #11  
Old April 16th, 2004, 03:28 PM
Andy Pope
external usenet poster
 
Posts: n/a
Default Line chart, value labeling

Just a little tweak to the code is needed.

Sub LastPointLabel()
Dim mySrs As Series
Dim nPts As Long
For Each mySrs In ActiveChart.SeriesCollection
With mySrs
nPts = .Points.Count
mySrs.Points(nPts).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
mySrs.Points(nPts).DataLabel.Text = mySrs.Values ' **
End With
Next
End Sub

Cheers
Andy

NYBoy wrote:

Gentlemen, It doesn't give the mathematical value. Instead, it gives me
the legand of the line (e.g. Monthly Performace).
I am looking for the value of the last point.
By the ways, you guys are great help...I learn a lot from here...


*********************************************
Line chart, value labeling
If you have the workbook with the chart open use ALT+F11 to get to the
VBE (Visual Basic Editor/Environment).
Use the Insert menu to add a Module.
Paste the code from Jon's page.

ALT+F11 to return to your workbook.
select the chart and the press ALT+F8 to display the Macro dialog.
From here run the LastPointLabel Macro.

To 'close' the thread just post a reply to say the solution worked.

Cheers
Andy

NYBoy wrote:


---
Message posted from http://www.ExcelForum.com/


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
  #12  
Old April 16th, 2004, 04:12 PM
NYBoy
external usenet poster
 
Posts: n/a
Default Line chart, value labeling

Andy, It worked. It gave me the value of the last point (for month
April) but.....

Just to test...As I entered the new data in the spreadsheet for next
month, those values didn't role to the next point.
I was expecting that it would give me new values for the new last
point...
Sorry to bother you again..


---
Message posted from http://www.ExcelForum.com/

  #13  
Old April 16th, 2004, 04:47 PM
Andy Pope
external usenet poster
 
Posts: n/a
Default Line chart, value labeling

You did run the macro again once the data changed.
Also note the additional code line to clear any previous labels.

Sub LastPointLabel()
Dim mySrs As Series
Dim nPts As Long
For Each mySrs In ActiveChart.SeriesCollection
With mySrs
.HasDataLabels = False ' Remove previous labels
nPts = .Points.Count
mySrs.Points(nPts).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
mySrs.Points(nPts).DataLabel.Text = mySrs.Values ' **
End With
Next
End Sub


NYBoy wrote:

Andy, It worked. It gave me the value of the last point (for month
April) but.....

Just to test...As I entered the new data in the spreadsheet for next
month, those values didn't role to the next point.
I was expecting that it would give me new values for the new last
point...
Sorry to bother you again..


---
Message posted from http://www.ExcelForum.com/


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
  #14  
Old April 16th, 2004, 06:19 PM
NYBoy
external usenet poster
 
Posts: n/a
Default Line chart, value labeling

Andy, It worked great. I copied your new code and pasted and run the
macro again....

But, still my goal is not to touch the chart at all. Can we also set so
that the marco would run automatically.....
If there is no way, then I can live with whatever I have so far...

but another thing is, everytime I run the marcos, text size, color,
etc... changes. Is there a permanent fix?


---
Message posted from http://www.ExcelForum.com/

  #15  
Old April 16th, 2004, 07:22 PM
NYBoy
external usenet poster
 
Posts: n/a
Default Line chart, value labeling

Andy, It worked great. I copied your new code and pasted and run the
macro again....

But, still my goal is not to touch the chart at all. Can we also set so
that the marco would run automatically.....
If there is no other way, then I can live with whatever I have so
far...

but another thing is, everytime I run the marcos, text size, color,
etc... changes. Is there a permanent fix?


---
Message posted from http://www.ExcelForum.com/

  #16  
Old April 17th, 2004, 04:48 PM
Andy Pope
external usenet poster
 
Posts: n/a
Default Line chart, value labeling

You will need to add the formatting to the macro. Use the macro recorder
whilst resetting the information to get the code required.

I have added a page to my site that shows how to label the last point
using dummy data series. This maybe more appropriate if you have dynamic
charts.
http://www.andypope.info/charts/Labellast.htm

Cheers
Andy

NYBoy wrote:

Andy, It worked great. I copied your new code and pasted and run the
macro again....

But, still my goal is not to touch the chart at all. Can we also set so
that the marco would run automatically.....
If there is no other way, then I can live with whatever I have so
far...

but another thing is, everytime I run the marcos, text size, color,
etc... changes. Is there a permanent fix?


---
Message posted from http://www.ExcelForum.com/


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
  #17  
Old April 19th, 2004, 05:25 PM
Andy Pope
external usenet poster
 
Posts: n/a
Default Line chart, value labeling

Yes, email it to me off list.

NYBoy wrote:

Andy, Can I send you the file? May be you can do it the quick way.


---
Message posted from http://www.ExcelForum.com/


--

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 Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:06 PM.


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