Labelling column chart bars with Legend descriptions?

sagain2k

Board Regular
Joined
Sep 8, 2002
Messages
94
If I create a simple column chart of this data, with the Data Range series in "Rows" (Note that the "numbers" in the # column were entered as Text values using the ' in front of them to avoid the chart thinking it's another data series):

# Jan Feb
1 10 20
2 30 40
3 50 60

Is there any way to have Excel allow you to put the Legend Key Label desriptions (in this case the "1", "2" and "3") on top of each column bar instead of just the Legend Key items (the colored boxes) itself? In other words, the bar for Jan AND the one for Feb representing row #1 data should each have a "1" on top of the bar (or underneath would work too), etc...

For the Data Labels, it lets you "Show Values", or "Show Label", or include "Legend Key next to Label", but none of that will result in each bar having the description "1", "2", "3" on top of it. What they needed was another check box for "Legend Key Description next to Label".

Here's the code for creating the chart if the data is in range A3:C6:

Range("A3:C6").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetSourceData Source:=Sheets _
("Sheet1").Range("A3:C6"), PlotBy:=xlRows

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabel, LegendKey:=True

The only available DataLabel types were:
"BubbleSizes", "Label", "LabelandPercent", "None", "Percent", or "Value".

Can anyone think of a way to automate the process of sequentially numbering the bars in a Column chart, or have VBA stick them in on top of each bar somehow as a work-around?
Am I missing something simple? I do NOT want to have to add them manually using Text Boxes! Thanks for any suggestions! This is a really valuable forum!

--Ray
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Ray
Try this:

Sub AddLabels()
Dim itm
Dim i
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue
For i = 1 To ActiveChart.SeriesCollection.Count
For Each itm In ActiveChart.SeriesCollection(i).DataLabels
itm.Text = ActiveChart.SeriesCollection(i).Name
Next itm
Next i
End Sub

Is this what you need?
Simon
 
Upvote 0
Simon,Your code snippet also helped part of my problem. Two remaining issues.
First: Can the labels dynamically be colored so they are not hidden and dynamically sized so they do not bust out of their variable information.[OK..that is kind of 2 problems]
Second: If my label information source cells are color coded with each variable[location in this case]cell formatted in a specific color, can this color dynamically define the legend color for the variable, i.e. myCity1 cell is orange, myCity2 cell is blue. Anytime myCity1 shows up in the chart, its source cell color defines the corresponding series area on the chart?
If the user defined legend cannot be self referential, can it at least be defined by a look up table of sorts and linked to the chart data points.
I am working with the stacked bar charts, changing over 3000 point series color and adding a text box to each one...all by hand. Never again say I...any ideas/knowledge?

thanks,
Estib
This message was edited by estibhoff on 2002-10-08 15:09
This message was edited by estibhoff on 2002-10-09 11:33
This message was edited by estibhoff on 2002-10-09 11:37
 
Upvote 0
One more issue. I ran the script on a pre-existing chart object and the label text is crammed into a narrow strip. I cannot seem to size it. Is this ability restricted to VBA code?
 
Upvote 0
Simon,

Many thanks! Your solution was exactly what I was looking for! It will put whatever the Legend Label descriptions are right on top of the corresponding bars...so when those descriptions are created from integers entered as text values, it has the effect of numbering the bars. Nicely done!!

--Ray
 
Upvote 0
Ray - glad the code helped you out.
Estib - I've not considered the problems you're trying to overcome before - I'll have a think & see if I can come up with anything.

Simon
 
Upvote 0
Simon,

I'm looking too. FYI, if you can figure it out, I can email you a description of the type of issue that the solution addresses. You may be able to create a tool kit of the labeling and legending that would be avaluable package for distribution.

Estib
 
Upvote 0
Simon...here's another question: is it possible to have the Label descriptions be attached at the bottom of each bar instead of the top? Just curious...and if you guys figure out how to have the colors automatically match the corresponding bar color, that would be icing on the cake (wonder if you could have that description text also be virtically oriented instead of horizontal?). As it is now, it's still VERY useful!
 
Upvote 0
Ray,
The following slightly amended code will add the labels at the bottom of each column, albeit still above the axis.

Sub AddLabels()
Dim itm
Dim i
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue
For i = 1 To ActiveChart.SeriesCollection.Count
For Each itm In ActiveChart.SeriesCollection(i).DataLabels
itm.Text = ActiveChart.SeriesCollection(i).Name
itm.Position = xlLabelPositionInsideBase
Next itm
Next i
End Sub

Or did you want to add the labels below the horizontal axis (a bit harder to do I expect!)

Also, I've had a first stab at one of Estib's questions - to colour code each series based on the colour of the label cell.

Sub ApplySeriesColors()
Dim c As Integer
Dim i As Integer
Dim r As Range
Dim bc()
i = 0
' series label range
Set r = Range("a1:a10")
'
ReDim bc(r.Count)
' store pattern colour for each series label cell
For Each itm In r
bc(i) = itm.Interior.ColorIndex
i = i + 1
Next itm
' apply stored pattern colours to active chart
For i = 1 To ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(i).Interior.ColorIndex = bc(i - 1)
Next
End Sub

The label range is hard coded into the macro, but it would be easy to add a userform with a refedit control so that this could be selected at run time.
The code does not create a link back to the series name cells, so if the cell colours changed the code would need to be re-run. Also it will not work with cells that are formatted using excel's conditional formatting feature.

Simon
 
Upvote 0
Simon...I discovered an interesting wrinkle in your "AddLabels" routine. If you use it for a bar chart created from data that has a series missing, such as this Jan-Mar test data with no data for Feb:

#|Jan|Feb|Mar
1|10 | |20
2|30 | |40
3|50 | |60

Then it also places the Label decsriptions piled on top of each other at the top left of the chart in addition to on top of each bar...it must be taking the labels meant for the empty series and dumping them there. I'm working with data that frequently has a series missing like that. Any idea on how to have it ignore the labeling when there is no data in a series? Probably need an If statement to test if a series is empty before proceding to the Next i? Let me know if you have a way to prevent those from being put at the top right... Cheers..and thanks for all the great code!

--Ray
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top