macro for automatically creating data labels in excel chart

Erik Houtriet

Hi all,

I'd like to write a macro in for excel to create automatically a chart, i have no experience in this field.

I use the following code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8-6-2006 by CTW
'
' Keyboard Shortcut: Ctrl+m
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R1C1:R2C1"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R1C2:R2C2"
End Sub

This code works well, but i want the following to do:

every time pressing Ctrl+m I want the used data for the chart move 2 rows below, so I can visually scroll through the data.

for example:

Ctrl+m gives chart of:

x value: Sheet1!R1C1:R2C1"
y value: Sheet1!R1C2:R2C2"

next time pressing Ctrl+m gives chart of:
x value: Sheet1!R3C1:R4C1"
y value: Sheet1!R3C2:R4C2"

next time pressing Ctrl+m gives chart of:
x value: Sheet1!R5C1:R6C1"
y value: Sheet1!R5C2:R6C2"

etc.

How do I program this in a macro I need it for my study, I'am a student civil engineering on the university of Twente, Netherlands.

Tnxs!!

Erik Houtriet.

Navajo

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

hysenlici

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

Andy Pope

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.

hysenlici

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

Andy Pope

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

hysenlici

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!

Andy Pope

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

hysenlici

Perfect,

many, many thanks

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

thanks again:)

macro for automatically creating data labels in excel chart

Re: macro for automatically creating data labels in excel chart

Re: macro for automatically creating data labels in excel chart

Re: macro for automatically creating data labels in excel chart

Re: macro for automatically creating data labels in excel chart

Re: macro for automatically creating data labels in excel chart

Re: macro for automatically creating data labels in excel chart

Re: macro for automatically creating data labels in excel chart

Re: macro for automatically creating data labels in excel chart