REPOST:Assigning labels to chart segments

G

Greg Kraushaar

(I am beginning to think my normal news feed is not letting my new posts through)
(Best viewed with a non proportional font)
(My apologies for the long post)
Access 2000 SP3

I am trying to float the row headings against against each
segment in a bar chart. Type = 100% Column.

If I try the FormatDataSeries value, I get the value ((as expected)
If I try the FormatDataSeries Label, I get the column headings
(Unexpected)

I have tried to set the labels through code by doing a pass on the
datasource,and get the labels, then place them on label.
It works, but they are not in the correct order.

DataSource:
Tactic| Jan | Feb | Mar *

N1 | 100 | 50 | 25
N2 | 200 | | 25
N3 | | 25 | 25
*Actually called Month1...Month3

Chart:
(What I am trying to achieve)

---- ---- -----
| |N1 | | | |N1
| | | | |---|
|--| | |N1 | |
| | | | | |N2
| | | | | |
| |N2 |--| |---|
| | | |N3 | |N3
| | | | | |
__________________________
Jan Feb Mar

Ideally I would like to read the Segment legend directly,
but am unable to get the correct syntax, if it is available
so I can get rid of the first chunk of code,
but even help sorting out the ordering problem would help

what
I have done so far is

To get the labels:
Set db = CurrentDb
Set RST = db.OpenRecordset("zztblBudgetSummaryTacticConcatenated")
If RST.EOF Then Err.Raise eAbort '-------------------------> No data
RST.MoveLast
ReDim aSerCol(1 To 22, 1 To 3)
RST.MoveFirst
i = 1
Do Until RST.EOF
For j = 1 To 3
If IsNull(RST.Fields("Month" & j)) Then 'removes blank labels
aSerCol(i, j) = " "
Else
aSerCol(i, j) = RST!Tactic
End If
Next
i = i + 1
RST.MoveNext
Loop


To Place them on the chart:
Set myChart = Me.OLEUnbound5
For i = 1 To myChart.SeriesCollection.Count
With myChart.SeriesCollection(i)
myChart.SeriesCollection(i).ApplyDataLabels _
xlDataLabelsShowValue, True
For j = 1 To myChart.SeriesCollection(i).Points.Count
myChart.SeriesCollection(i).ApplyDataLabels _
xlDataLabelsShowValue, True
With myChart.SeriesCollection(i).Points(j)
.HasDataLabel = True
.Datalabel.HorizontalAlignment = xlCenter
.Datalabel.Font.Size = 8
.Datalabel.Font.Bold = False
If Len(aSerCol(i, j)) > 0 Then
.Datalabel.Text = aSerCol(i, j)
.Datalabel.Text = aSerCol(i, j)
.Datalabel.Left = .Datalabel.Left + 22
End If
End With
.DataLabels.ShowLegendKey = False
.DataLabels.Font.Size = 8
Next j
End With
Next i
 
G

Greg Kraushaar

Sorted!!! :)>
And yes my news feed has been playing up.
I now get my MS news feed direct form MS, so hopefully no more
problems on that front.

For those interested, the problem was that I was going directly to the
table for both the Label setting and the Chart. They were setting
different orders.
I resolved the issue by using a query instead, and specifying an order
in that query.

I have also done a general cleanup on the code
My final (subject to final cleanup) follows:

Set RST = db.OpenRecordset("qselTacticBar")
If RST.EOF Then Err.Raise eAbort '---------------------> No data
RST.MoveLast
ReDim aSerCol(1 To RST.RecordCount, 1 To cMonthCount)
RST.MoveFirst
i = 1
Do Until RST.EOF
For j = 1 To cMonthCount
If (RST.Fields("Month" & j) = 0) Then
aSerCol(i, j) = ""
Else
aSerCol(i, j) = RST!Tactic
End If
Next
i = i + 1
RST.MoveNext
Loop

Set myChart = Me.OLEUnbound5
For i = 1 To i - 1 'This is effectively UBound(aSerCol)
'But as aSerCol was DIMmed as Variant.
' The compiler wouldn't let me use it
With myChart.SeriesCollection(i)
For j = 1 To .Points.Count
With .Points(j)
.HasDataLabel = True
.Datalabel.HorizontalAlignment = xlCenter
.Datalabel.VerticalAlignment = xlCenter 'We will move this
' soon, but ensure starting point
.Datalabel.Font.Size = 8
.Datalabel.Font.Bold = False
If Len(aSerCol(i, j)) > 0 Then
.Datalabel.Text = aSerCol(i, j)
.Datalabel.Left = .Datalabel.Left + 35
'35 is an arbitrary number that works on my machine!
Else
.Datalabel.Text = " "
.HasDataLabel = False
End If
End With
Next
End With
Next i
 
Top