'Ray' chart

[steven.gibbs]

I have created a ray or radial chart which has data from the origin
(0,0) to a point at (x,y).

This creates something like the hands of a clock but with many more
'hands' of different lengths.

At the end of each ray I have a data label.

Is it possible to line-up the text as an extension of the ray? i.e.
put the text at an angle

 

[Jon Peltier]

You can change the orientation of the text to any angle between -90 and +90,
and you can position the labels pretty much anywhere within the plot area.
The orientation cannot be done automatically, but I had a project which
would update the label orientation whenever the chart source data changed.

- Jon

 

[steven.gibbs]

The orientation cannot be done automatically, but I had a project which

would update the label orientation whenever the chart source data changed.

- Jon

From the two points I can calculate the angle for the label ...

How did you change the label orientation to match the source data?
(you say it cannot be automatic but then say
'update ...orientation'?).

Steve

 

[Jon Peltier]

I had a VBA procedure which ran when the cells changed. It was fired by the
Worksheet_Change event. So it wasn't done automatically within the Excel UI,
but it was done automatically with a little help from VBA.

- Jon

 

[steven.gibbs]

Is this complex code? I have precious little experience in VB and
would need some hints to get started!

 

[Andy Pope]

Hi,

Here is my version of the code to radiate your text labels.
Assumes you 1 series has data labels applied. Select chart and run code

Sub MakeSpokeText()

Dim lngPoint As Long
Dim sngX As Single
Dim sngY As Single
Dim dblPI As Double
Dim sngAngle As Single

dblPI = Application.WorksheetFunction.Pi

With ActiveChart
With .SeriesCollection(1)
For lngPoint = 1 To .Points.Count
sngX = Application.WorksheetFunction.Index( _
..XValues, 1, lngPoint)
sngY = Application.WorksheetFunction.Index( _
..Values, 1, lngPoint)
If IsNumeric(sngX) And IsNumeric(sngY) Then
If sngX = 0 And sngY = 0 Then
' ignore center points
Else
sngAngle = Atn(sngY / sngX) * (180 / dblPI)
.Points(lngPoint).DataLabel.Orientation = _
sngAngle
End If
End If
Next
End With
End With

End Sub

Cheers
Andy

 

[steven.gibbs]

Great!

Thanks

Steve