Labelling column chart bars with Legend descriptions?

[sagain2k]

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

 

[sagain2k]

I'm experiementing with something like this for your "AddLabels" routine, which isn't working, so I don't know the syntax to set the condition where the series(i) is empty. I don't want to use Zero because that might actually be a legitimate value in some cases. Am I on the right track?

If ActiveChart.SeriesCollection(i).Value <> "" Then

itm.Text = ActiveChart.SeriesCollection(i).Name

End If

 

[estibhoff]

Simon,

most excellent! I am such a newbie to VBA, I am at a loss for where to look. I was attempting use LegendEntries, etc... to try to trigger a chart series color change. I gave your code a whirl and it worked splendidly. I may try to build on this and see if a standard legend source [names in colored cells on a solitary worksheet] can be referenced and the value of the series triggers the color change. The goal is to be able to update 13 worksheets that have 5 chart objects apiece.
Any suggestions on VBA programming or reference books that would help me learn VBA specifically for Excel?
Thanks again.
Estib

 

[SimonP]

Ray,

Try this amended version....

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

If itm.Text <> "0" And itm.Text <> "" Then
itm.Text = ActiveChart.SeriesCollection(i).Name
itm.Position = xlLabelPositionInsideBase
Else
itm.Text = ""
End If
Next itm
Next i
End Sub

Simon

 

[SimonP]

Estib,
The book that helped me get into VBA is John Walkenbach's Excel 97 Programming for Windows for Dummies. Its a great introduction to most of the basic VBA programming concepts (IMO!)
John's web site is also a useful source of info & ideas http://www.j-walk.com/ss

Simon

 

[estibhoff]

Thanks Simon,

I may order the book [or related] today and delve into the dark secrets.

Steve
This message was edited by estibhoff on 2002-10-10 11:43

 

[sagain2k]

Simon...once again, nicely done! Very useful solution to omit cases where the items in a series are blank or zero. One last question for you on this topic: can you think of a way to force each bar to be a particular width? In one case for my bar chart, I have 10 different series that get plotted. Each bar is quite thin, and the Data Label descriptions that are 2 digits (the "10" in my case) can only display the one digit.

What I'd like to do is force each bar to be wider, which would make the overall chart wider as well of course. I tried playing with changing the "overlap" and the "gap width" in the Options tab of the Format Data Series dialog box, but that doesn't do it. Think it's possible to control each bar series width? Thanks...awesome assistance, and every example adds to a growing arsenal of programming tools...much appreciated!!

By the way, I'm also always on the lookout for good VBA resources manuals etc..., and especially any that have clear and comprehensive examples of how to specify all the different objects and what properties of them can be modified. Most books I've seen aren't very comprehensive that way...I'll check out your suggestions as well. Thanks again,

--Ray

 

[SimonP]

Ray,
Glad the code helped. You should be able to widen all of the series bars by reducing the gap width - although this won't change the overall size of the chart - it will just reduce the gaps between each bar - effectively making them wider. You could also try reducing the font size of the labels? I don't think you can control each bar width individually - it's all or nothing.

Simon

 

[estibhoff]

Ray--
In one book I found, it described what should have been a no-brainer idea to start figuring out which objects are available for coding...record a macro while examining the objects your interested in affecting, then examine the code. This may not inform about all objects, but it is a good start in conjunction with the Object Browser and a good VBA book.
I did not even think to do the obvious. I am sure this idea is posted throughout the forum and in many books, but thought I would toss it out there.
I just found the Excel 2000VBA Programmer's reference by John Green. It was $25 bucks and has what I need to begin. I wanted to buy the John Walkenbach book on Programming VBA, but my limit was $25. If only there were a building full of books that let you borrow books for a time..

-estib

 

[sagain2k]

Simon..thanks for the response. The gab width can't really solve the bar width issue for me, but changing the label font size can be helpful. Thanks again for all the great assistance!

Estib...recording macros and examining the code is certainly an invaluable tool, which I always take advantage of. One problem with that is that it doesn't always capture the code for all the actions that you are able to perform while recording. But still, it is one of the faster ways to see how objects are specified in code. Thanks for the response...Cheers,

--Ray