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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|