macro for automatically creating data labels in excel chart

macro for automatically creating data labels in excel chart

• MRandall

  Please help me with this,

  I have no experience working with macros. Someone will be appreciated if help with this:

  Create a macro that labels data point is a series. The homework is:

  X axes are 12 months. The chart type is line, which usually is parallel with x axes. But sometime the value changes and the line changes automatically. I WANT THE FIRST MOTH A LABEL ON JANUARY AND IF THE VALUE DOESN'T CHANGE OVER MONTHS, THAT WOULD ME ONLY ONE LABEL. IF THE VALUE CHANGES, THEN A LABEL WOULD BE AUTOMATICALLY PLACES ONLY ON THE DATA POINT THAT THE VALUE HAS CHANGED.

  In other words, when the line is paralel, one lable only, when the line changes, FIRST MONTH LABEL + LABEL EVREY MONTH THAT CHANGE OCCURS.

  YOUR HELP IS HIGHLY APPRECIATED

  
  

• Nfrf

  Thanks for your prompt help.

  My chart is a PIVOT CHART and I can't make changes. Can you express this logic in a macro I will click a button to apply macro after I have refreshed the pivot chart.

  One again: Lets' say I have 12 month on X axis: Jan, Feb, March,........December.

  Lets' assume the value on the column that represents January is $200.00 and so is February and March. My series is a parallel line with X-axis and I want macro to place a label in JANUARY ONLY.

  Let's assume again that April to September value went up to $250.00. Now I want that the same macro applied to show labels in JANUARY AND SEPTEMBER ONLY, and so on. No need for label in December.

  Can you apply your logic in this macro

  Thank you again

  
  

• BMcDowell

  Does your homework specify you must use code
  
  It can be done just using formula and 2 data series.
  
  Assume months and fixed value are in A1:B13
  Add these formula
  
  C2: =B2
  C3: =IF(B3=$B$2,NA(),B3)
  
  drag C3 down to C13.
  
  Create a line chart on the range A1:C13.
  Format the second series to have no markers or line. Apply Category data labels to this series.
  
  Jan should have a label and so will any other month where the value in B does not match that of B2.
  
  
  

• David Cautley

  It works for me with more or less than 12 categories. There is nothing in the code that explicitly uses 12. It loops enough times for the data.
  
  It is possible empty data series will cause a error, in which case you can add the line after the variable declaration lines.
  
  On Error Resume Next
  
  
  
  

• Jose76

  One way is to store the row start and end values on a sheet and plug them into the chart series strings. I've defined two named ranges, RowStart and RowEnd on Sheet1 and set their initial values to 1 and 2 respectively.
  
  Sub Macro1()
  Dim rowStart As Variant
  Dim rowEnd As Variant
  
  rowStart = Range("Sheet1!RowStart") 'get row values
  rowEnd = Range("Sheet1!RowEnd")
  
  ActiveSheet.ChartObjects("Chart 1").Activate
  ActiveChart.ChartArea.Select
  
  ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R" & rowStart & "C1:R" & rowEnd & "C1"
  ActiveChart.SeriesCollection(1).Values = "=Sheet1!R" & rowStart & "C2:R" & rowEnd & "C2"
  
  Range("Sheet1!rowStart") = RowStart + 2 'store next row values
  Range("Sheet1!rowEnd") = RowEnd + 2
  
  End Sub
  
  To start with initial values when the workbook opens, you could use this code:
  
  Private Sub Workbook_Open()
  Range("Sheet1!rowStart") = 1
  Range("Sheet1!rowEnd") = 2
  End Sub
  
  
  
  

• luissol

  Sub XX()
  Dim objSeries As Series
  Dim lngIndex As Long
  Dim vntValue As Variant
  Dim vntBaseValue As Variant
  
  Set objSeries = ActiveChart.SeriesCollection(1)
  For Each vntValue In objSeries.Values
  lngIndex = lngIndex + 1
  If lngIndex = 1 Then
  vntBaseValue = vntValue
  objSeries.Points(lngIndex).ApplyDataLabels AutoText:=True, _
  LegendKey:=False, ShowSeriesName:=False, ShowCategoryName:=True, _
  ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
  Else
  If vntValue <> vntBaseValue Then
  objSeries.Points(lngIndex).ApplyDataLabels AutoText:=True, _
  LegendKey:=False, ShowSeriesName:=False, ShowCategoryName:=True, _
  ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
  End If
  End If
  Next
  
  End Sub
  
  

• Aleniko29139

  Perfect,

  many, many thanks

  I think other users will need this macro, it is very comon and useful,

  thanks again:)

  
  

• LPlate

  Excitedly worked well.

  Thank you very much

  Another thing to be added. When I have a chart with 12 month as columns, works very well, When lest than 12 columns, doesn't work. Could you fix for me

  Appreciations!

  
  
• macro for automatically creating data labels in excel chart