Using VBA to display arbitrary data labels on a chart

One of the most frequent complaints about Excel's charting is its inflexible data labeling feature. For example, consider the XY chart in Figure 18-7. It might be useful to display the associated name for each data point. However, you can search all day, and you'll never find the Excel command that lets you do this automatically. (Hint: Such a command doesn't exist.) Data labels are limited to the values only . . . unless you want to edit each data label manually and replace it with text of your choice.

FE

A

B C

D

E

FIG H I I J

K

i

Befoie After

2

Anne

156

119

300

3

Bob

209 289

4

Chuck

194

210

5

David

278

274

250

7

Francine

201

177

a

George

22C

21S

9

Hanna

165

161

C 200

10

150

J

14

16

1fi

17

18

Before

20

I I I I I I

|m

► gfc Sheet 1 /

J_<J I

Figure 18-7: An XY chart with no data labels.

Figure 18-7: An XY chart with no data labels.

Listing 18-2 presents a simple procedure that works with the first chart on the active sheet. It prompts the user for a range and then loops through the Points collection and changes the Text property to the values found in the range.

Listing 18-2: Retrieving Data Point Labels from Field Names in The Worksheet

Sub DataLabelsFromRange() Dim DLRange As Range Dim Cht As Chart Dim i As Integer

' Specify chart

Set Cht = ActiveSheet.ChartObjects(1).Chart

' Prompt for a range On Error Resume Next Set DLRange = Application.InputBox _

(prompt:="Range for data labels?", Type:=8) If DLRange Is Nothing Then Exit Sub On Error GoTo 0

' Add data labels

Cht.SeriesCollection(1).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, _ LegendKey:=False

' Loop through the Points, and set the data labels Pts = Cht.SeriesCollection(1).Points.Count For i = 1 To Pts

Cht.SeriesCollection(1). _

Points(i).DataLabel.Text = DLRange(i)

Next i End Sub

This example is available on the companion CD-ROM.

Figure 18-8 shows the chart after running the DataLabelsFromRange procedure and specifying A2:A9 as the data range.

EE

A B

C

D

E I

F I G I H I

r-

1

After

2

Anne 156

119

3

Bob 209

289

4

Chuck 194

210

■ D.ivld

5

David 278

274

250 •

7

Francine 201

177

8

George 220

218

Hanna 165

161

200 •

10

11

■ Fi ni' in'. :

12

■ ihinni >EllSi

13

14

16

■ Anne

1H

17

10

>ir

► n|\Sheetl /

J±l

1

Figure 18-8: This XY chart has data labels, thanks to a VBA procedure.

The preceding procedure is rather crude and does very little error checking. In addition,it only works with the first Series object.The Power Utility Pak (which you can obtain by using the coupon in the back of the book) includes a much more sophisticated chart data-labeling utility.

0 0

Post a comment