I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area is 422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a point, what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

This doesn't specifically answer your question, but Rob Bovey has written a
free utility, xy Chart labeller. I don't believe the code is protected, so
you can possibly look at that for ideas:

http://www.appspro.com/Utilities/Utilities.htm

--
Regards,
Tom Ogilvy


"Phil Stanton" wrote:

I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area is 422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a point, what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

This problem is similar to a macro I run at work all the time. I needed to
drop a line on a chart at a particular X value. I used simple ratios as
expalined below

Suppose your x axis goes from 10 to 100 and you need to find the value of 50
then

X_Ratio = (50 - 10)/(100 - 10)

Then if the pixel values of the chart goes from 1000 to 2000. You can get
thiese numbers from the .left parameter and the .width parameter of the chart.

X_Pixel = (X_Ratio * (2000 - 1000)) + 1000

"Phil Stanton" wrote:

I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area is 422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a point, what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

Sound what I need, thanks

How do I get the pixle values as I thought chart dimensions were in points

Phil


"Joel" wrote in message
...
This problem is similar to a macro I run at work all the time. I needed
to
drop a line on a chart at a particular X value. I used simple ratios as
expalined below

Suppose your x axis goes from 10 to 100 and you need to find the value of
50
then

X_Ratio = (50 - 10)/(100 - 10)

Then if the pixel values of the chart goes from 1000 to 2000. You can get
thiese numbers from the .left parameter and the .width parameter of the
chart.

X_Pixel = (X_Ratio * (2000 - 1000)) + 1000

"Phil Stanton" wrote:

I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the
chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area is
422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a point,
what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

Points and pixels are the same. Sorry for the confusion.

"Phil Stanton" wrote:

Sound what I need, thanks

How do I get the pixle values as I thought chart dimensions were in points

Phil


"Joel" wrote in message
...
This problem is similar to a macro I run at work all the time. I needed
to
drop a line on a chart at a particular X value. I used simple ratios as
expalined below

Suppose your x axis goes from 10 to 100 and you need to find the value of
50
then

X_Ratio = (50 - 10)/(100 - 10)

Then if the pixel values of the chart goes from 1000 to 2000. You can get
thiese numbers from the .left parameter and the .width parameter of the
chart.

X_Pixel = (X_Ratio * (2000 - 1000)) + 1000

"Phil Stanton" wrote:

I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the
chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area is
422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a point,
what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

Thanks for replying

Now even more confused

As far as I know, points atre 1/72 of an inch and pixels are all to do with
screen resolution

This routine, nicked from Stephen Lebans, returns 96 for my screen
resolution (Pixels per inch??????)

Phil

Option Explicit


Private Declare Function apiCreateIC Lib "gdi32" Alias "CreateICA" _
(ByVal lpDriverName As String, ByVal lpDeviceName As String, _
ByVal lpOutput As String, lpInitData As Any) As Long

Private Declare Function apiGetDeviceCaps Lib "gdi32" _
Alias "GetDeviceCaps" (ByVal hDC As Long, ByVal nIndex As Long) As Long


Private Declare Function apiDeleteDC Lib "gdi32" _
Alias "DeleteDC" (ByVal hDC As Long) As Long


Private Const LOGPIXELSX = 88
Global Const TwipsPerMM = 56.7
Global Const PointsPerMM = 2.83

Dim lngXdpi As Long


Function GetScreenResolution() As Long

Dim lngIC As Long

'Get Screen resolution
lngIC = apiCreateIC("DISPLAY", vbNullString, vbNullString, vbNullString)
'If the call to CreateIC didn't fail, then get the Screen X resolution.
If lngIC < 0 Then
lngXdpi = apiGetDeviceCaps(lngIC, LOGPIXELSX)
GetScreenResolution = lngXdpi
'Release the information context.
apiDeleteDC (lngIC)
Else
' Something has gone wrong. Assume an average value.
lngXdpi = 120
GetScreenResolution = lngXdpi
End If

End Function


"Joel" wrote in message
...
Points and pixels are the same. Sorry for the confusion.

"Phil Stanton" wrote:

Sound what I need, thanks

How do I get the pixle values as I thought chart dimensions were in
points

Phil


"Joel" wrote in message
...
This problem is similar to a macro I run at work all the time. I
needed
to
drop a line on a chart at a particular X value. I used simple ratios
as
expalined below

Suppose your x axis goes from 10 to 100 and you need to find the value
of
50
then

X_Ratio = (50 - 10)/(100 - 10)

Then if the pixel values of the chart goes from 1000 to 2000. You can
get
thiese numbers from the .left parameter and the .width parameter of the
chart.

X_Pixel = (X_Ratio * (2000 - 1000)) + 1000

"Phil Stanton" wrote:

I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the
chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area is
422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a
point,
what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

Points & pixels are not the same. A Pixel is a dot on a screen, a Point is
1/72 inches (kind of and dates back to early printing days). Their relative
sizes may vary according to system and in particular system font size (eg
normal or large fonts). Typically in most systems with "normal" fonts one
pixel = 0.75 points or rather 96 dpi or 72/96, though a few API calls can
calculate if/as required.

Regards,
Peter T

"Joel" wrote in message
...
Points and pixels are the same. Sorry for the confusion.

"Phil Stanton" wrote:

Sound what I need, thanks

How do I get the pixle values as I thought chart dimensions were in
points

Phil


"Joel" wrote in message
...
This problem is similar to a macro I run at work all the time. I
needed
to
drop a line on a chart at a particular X value. I used simple ratios
as
expalined below

Suppose your x axis goes from 10 to 100 and you need to find the value
of
50
then

X_Ratio = (50 - 10)/(100 - 10)

Then if the pixel values of the chart goes from 1000 to 2000. You can
get
thiese numbers from the .left parameter and the .width parameter of
the
chart.

X_Pixel = (X_Ratio * (2000 - 1000)) + 1000

"Phil Stanton" wrote:

I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the
chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area
is
422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a
point,
what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

If for example I want to place a particular label exactly over a point,

I know that's not your real question but if that was, simply
ActiveChart.SeriesCollection(1).DataLabels.Positio n = xlLabelPositionCenter

But if I follow, your real question is how to get the XY coordinates of your
XY points. Have a go with the following

Create an XY embedded chart with one series, keep it simple let each X:Y
make a straight line starting with X:Y = 0:0, 1:1 etc

Option Explicit
Sub test()
Dim i As Long
Dim cht As Chart
Dim sr As Series
Dim aX As Axis, aY As Axis
Dim x0 As Single, y0 As Single, xP As Single, yP As Single
Dim xf As Double, yf As Double
Dim arrX, arrY

Set cht = ActiveSheet.ChartObjects(1).Chart
Set aX = cht.Axes(1)
Set aY = cht.Axes(2)

x0 = aY.Left + aY.Width
y0 = aX.Top + aX.Height

yf = y0 - aY.Top
xf = (aX.Left + aX.Width - x0)

xf = (aX.Left + aX.Width - x0) / (aX.MaximumScale)
yf = (y0 - aY.Top) / aY.MaximumScale

Set sr = cht.SeriesCollection(1)
With sr
arrX = .XValues
arrY = .Values
For i = 1 To .Points.Count
xP = x0 + arrX(i) * xf
yP = y0 - arrY(i) * yf
With .DataLabels(i)
.Left = xP
.Top = yP
End With
Next
End With

End Sub

I know yesterday you said you are working with an MS Graph, I think it
should work pretty much the same way but try the above in Excel. First with
a straight line, then your "difficult" data.

The idea is to create your own co-ord system centred in the intersection of
the XY axes, then scale X & Y according to the length to right and above the
intersection. Where a chart does not have either of those axes would use
the plot-area instead.

I'll leave the hard part to you, as to how to work out quite where to place
those labels on a crowded chart. Actually I'd be interested to see!

Almost forgot to say, for some reason data labels don't seem to expose there
width & height properties, hence in this demo top-left label corners are
placed over points (if it works as intended). I imagine you'll be able to
guesstimate sizes depending on text & font, otherwise there's a much more
long winded way to get a good idea of the size.

Regards,
Peter T



"Phil Stanton" wrote in message
et...
I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the
chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area is 422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a point,
what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

Thanks

will have a look at it later

The annoying thing is I have an Excel program that extracts the data held in
Access and works perfectly ( and quickly). I then show the GIF file that
Excel generates back in the Access form. Trouble is I am a purist and want
to see a chart update dynamically as I change the Access data.

If you would like to see the GIF file generated from Excel I can email it to
you

Thanks again

Phil
"Peter T" <peter_t@discussions wrote in message
...
If for example I want to place a particular label exactly over a point,

I know that's not your real question but if that was, simply
ActiveChart.SeriesCollection(1).DataLabels.Positio n =
xlLabelPositionCenter

But if I follow, your real question is how to get the XY coordinates of
your
XY points. Have a go with the following

Create an XY embedded chart with one series, keep it simple let each X:Y
make a straight line starting with X:Y = 0:0, 1:1 etc

Option Explicit
Sub test()
Dim i As Long
Dim cht As Chart
Dim sr As Series
Dim aX As Axis, aY As Axis
Dim x0 As Single, y0 As Single, xP As Single, yP As Single
Dim xf As Double, yf As Double
Dim arrX, arrY

Set cht = ActiveSheet.ChartObjects(1).Chart
Set aX = cht.Axes(1)
Set aY = cht.Axes(2)

x0 = aY.Left + aY.Width
y0 = aX.Top + aX.Height

yf = y0 - aY.Top
xf = (aX.Left + aX.Width - x0)

xf = (aX.Left + aX.Width - x0) / (aX.MaximumScale)
yf = (y0 - aY.Top) / aY.MaximumScale

Set sr = cht.SeriesCollection(1)
With sr
arrX = .XValues
arrY = .Values
For i = 1 To .Points.Count
xP = x0 + arrX(i) * xf
yP = y0 - arrY(i) * yf
With .DataLabels(i)
.Left = xP
.Top = yP
End With
Next
End With

End Sub

I know yesterday you said you are working with an MS Graph, I think it
should work pretty much the same way but try the above in Excel. First
with
a straight line, then your "difficult" data.

The idea is to create your own co-ord system centred in the intersection
of
the XY axes, then scale X & Y according to the length to right and above
the
intersection. Where a chart does not have either of those axes would use
the plot-area instead.

I'll leave the hard part to you, as to how to work out quite where to
place
those labels on a crowded chart. Actually I'd be interested to see!

Almost forgot to say, for some reason data labels don't seem to expose
there
width & height properties, hence in this demo top-left label corners are
placed over points (if it works as intended). I imagine you'll be able to
guesstimate sizes depending on text & font, otherwise there's a much more
long winded way to get a good idea of the size.

Regards,
Peter T



"Phil Stanton" wrote in message
et...
I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the
chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area is
422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a point,
what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

From what you described yesterday about how slow that MS Graph have you
considered automating Excel (from Access), updating your Excel chart then
transferring an image of the chart via the clipboard onto an image control
on your Access display.

Try Stephen Bullen's PastePicture.xls which you'll find zipped on this page
http://www.oaltd.co.uk/Excel/Default.htm

It looks more complicated than it is, it should all work pretty fast for
you.

I don't know VBA in Access, I assume it does not directly expose a Clipboard
object but if it does you are in luck, you'd only need two lines of code
instead of the main module from PastePicture.

Regards,
Peter T

PS, since my last post I now realise there are other ways to get those label
sizes simply by moving to various preset positions around their respective
points - perhaps you get what I'm thinking of.



"Phil Stanton" wrote in message
et...
Thanks

will have a look at it later

The annoying thing is I have an Excel program that extracts the data held
in
Access and works perfectly ( and quickly). I then show the GIF file that
Excel generates back in the Access form. Trouble is I am a purist and want
to see a chart update dynamically as I change the Access data.

If you would like to see the GIF file generated from Excel I can email it
to
you

Thanks again

Phil
"Peter T" <peter_t@discussions wrote in message
...
If for example I want to place a particular label exactly over a point,

I know that's not your real question but if that was, simply
ActiveChart.SeriesCollection(1).DataLabels.Positio n =
xlLabelPositionCenter

But if I follow, your real question is how to get the XY coordinates of
your
XY points. Have a go with the following

Create an XY embedded chart with one series, keep it simple let each X:Y
make a straight line starting with X:Y = 0:0, 1:1 etc

Option Explicit
Sub test()
Dim i As Long
Dim cht As Chart
Dim sr As Series
Dim aX As Axis, aY As Axis
Dim x0 As Single, y0 As Single, xP As Single, yP As Single
Dim xf As Double, yf As Double
Dim arrX, arrY

Set cht = ActiveSheet.ChartObjects(1).Chart
Set aX = cht.Axes(1)
Set aY = cht.Axes(2)

x0 = aY.Left + aY.Width
y0 = aX.Top + aX.Height

yf = y0 - aY.Top
xf = (aX.Left + aX.Width - x0)

xf = (aX.Left + aX.Width - x0) / (aX.MaximumScale)
yf = (y0 - aY.Top) / aY.MaximumScale

Set sr = cht.SeriesCollection(1)
With sr
arrX = .XValues
arrY = .Values
For i = 1 To .Points.Count
xP = x0 + arrX(i) * xf
yP = y0 - arrY(i) * yf
With .DataLabels(i)
.Left = xP
.Top = yP
End With
Next
End With

End Sub

I know yesterday you said you are working with an MS Graph, I think it
should work pretty much the same way but try the above in Excel. First
with
a straight line, then your "difficult" data.

The idea is to create your own co-ord system centred in the intersection
of
the XY axes, then scale X & Y according to the length to right and above
the
intersection. Where a chart does not have either of those axes would
use
the plot-area instead.

I'll leave the hard part to you, as to how to work out quite where to
place
those labels on a crowded chart. Actually I'd be interested to see!

Almost forgot to say, for some reason data labels don't seem to expose
there
width & height properties, hence in this demo top-left label corners are
placed over points (if it works as intended). I imagine you'll be able
to
guesstimate sizes depending on text & font, otherwise there's a much
more
long winded way to get a good idea of the size.

Regards,
Peter T



"Phil Stanton" wrote in message
et...
I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the
chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area is
422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a point,
what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

Hi Peter

Thanks for code it works just fine, but is I resize the chart the label
positions go wrong.

Think I will abandon the project and just have to live with the Excel
version

Many thanks for your help thoug

Phil


"Peter T" <peter_t@discussions wrote in message
...
From what you described yesterday about how slow that MS Graph have you
considered automating Excel (from Access), updating your Excel chart then
transferring an image of the chart via the clipboard onto an image control
on your Access display.

Try Stephen Bullen's PastePicture.xls which you'll find zipped on this
page
http://www.oaltd.co.uk/Excel/Default.htm

It looks more complicated than it is, it should all work pretty fast for
you.

I don't know VBA in Access, I assume it does not directly expose a
Clipboard
object but if it does you are in luck, you'd only need two lines of code
instead of the main module from PastePicture.

Regards,
Peter T

PS, since my last post I now realise there are other ways to get those
label
sizes simply by moving to various preset positions around their respective
points - perhaps you get what I'm thinking of.



"Phil Stanton" wrote in message
et...
Thanks

will have a look at it later

The annoying thing is I have an Excel program that extracts the data held
in
Access and works perfectly ( and quickly). I then show the GIF file that
Excel generates back in the Access form. Trouble is I am a purist and
want
to see a chart update dynamically as I change the Access data.

If you would like to see the GIF file generated from Excel I can email it
to
you

Thanks again

Phil
"Peter T" <peter_t@discussions wrote in message
...
If for example I want to place a particular label exactly over a
point,

I know that's not your real question but if that was, simply
ActiveChart.SeriesCollection(1).DataLabels.Positio n =
xlLabelPositionCenter

But if I follow, your real question is how to get the XY coordinates of
your
XY points. Have a go with the following

Create an XY embedded chart with one series, keep it simple let each
X:Y
make a straight line starting with X:Y = 0:0, 1:1 etc

Option Explicit
Sub test()
Dim i As Long
Dim cht As Chart
Dim sr As Series
Dim aX As Axis, aY As Axis
Dim x0 As Single, y0 As Single, xP As Single, yP As Single
Dim xf As Double, yf As Double
Dim arrX, arrY

Set cht = ActiveSheet.ChartObjects(1).Chart
Set aX = cht.Axes(1)
Set aY = cht.Axes(2)

x0 = aY.Left + aY.Width
y0 = aX.Top + aX.Height

yf = y0 - aY.Top
xf = (aX.Left + aX.Width - x0)

xf = (aX.Left + aX.Width - x0) / (aX.MaximumScale)
yf = (y0 - aY.Top) / aY.MaximumScale

Set sr = cht.SeriesCollection(1)
With sr
arrX = .XValues
arrY = .Values
For i = 1 To .Points.Count
xP = x0 + arrX(i) * xf
yP = y0 - arrY(i) * yf
With .DataLabels(i)
.Left = xP
.Top = yP
End With
Next
End With

End Sub

I know yesterday you said you are working with an MS Graph, I think it
should work pretty much the same way but try the above in Excel. First
with
a straight line, then your "difficult" data.

The idea is to create your own co-ord system centred in the
intersection
of
the XY axes, then scale X & Y according to the length to right and
above
the
intersection. Where a chart does not have either of those axes would
use
the plot-area instead.

I'll leave the hard part to you, as to how to work out quite where to
place
those labels on a crowded chart. Actually I'd be interested to see!

Almost forgot to say, for some reason data labels don't seem to expose
there
width & height properties, hence in this demo top-left label corners
are
placed over points (if it works as intended). I imagine you'll be able
to
guesstimate sizes depending on text & font, otherwise there's a much
more
long winded way to get a good idea of the size.

Regards,
Peter T



"Phil Stanton" wrote in message
et...
I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the
chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area is
422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a
point,
what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

Afraid the code I posted was incomplete to say the least, although would be
OK with a typical Scatter chart with no values to below or to left of the
respective axes.

For my own interest I did a bit more to cater for the above and other
alignment/order options for axes, and also for non scatter charts, ie with
labels rather than X values. FWIW it seems reasonably reliable to work out
point coordinates on a chart.

Regards,
Peter T



"Phil Stanton" wrote in message
et...

Hi Peter

Thanks for code it works just fine, but is I resize the chart the label
positions go wrong.

Think I will abandon the project and just have to live with the Excel
version

Many thanks for your help thoug

Phil


"Peter T" <peter_t@discussions wrote in message
...
From what you described yesterday about how slow that MS Graph have you
considered automating Excel (from Access), updating your Excel chart
then
transferring an image of the chart via the clipboard onto an image
control
on your Access display.

Try Stephen Bullen's PastePicture.xls which you'll find zipped on this
page
http://www.oaltd.co.uk/Excel/Default.htm

It looks more complicated than it is, it should all work pretty fast for
you.

I don't know VBA in Access, I assume it does not directly expose a
Clipboard
object but if it does you are in luck, you'd only need two lines of code
instead of the main module from PastePicture.

Regards,
Peter T

PS, since my last post I now realise there are other ways to get those
label
sizes simply by moving to various preset positions around their
respective
points - perhaps you get what I'm thinking of.



"Phil Stanton" wrote in message
et...
Thanks

will have a look at it later

The annoying thing is I have an Excel program that extracts the data
held
in
Access and works perfectly ( and quickly). I then show the GIF file
that
Excel generates back in the Access form. Trouble is I am a purist and
want
to see a chart update dynamically as I change the Access data.

If you would like to see the GIF file generated from Excel I can email
it
to
you

Thanks again

Phil
"Peter T" <peter_t@discussions wrote in message
...
If for example I want to place a particular label exactly over a
point,

I know that's not your real question but if that was, simply
ActiveChart.SeriesCollection(1).DataLabels.Positio n =
xlLabelPositionCenter

But if I follow, your real question is how to get the XY coordinates
of
your
XY points. Have a go with the following

Create an XY embedded chart with one series, keep it simple let each
X:Y
make a straight line starting with X:Y = 0:0, 1:1 etc

Option Explicit
Sub test()
Dim i As Long
Dim cht As Chart
Dim sr As Series
Dim aX As Axis, aY As Axis
Dim x0 As Single, y0 As Single, xP As Single, yP As Single
Dim xf As Double, yf As Double
Dim arrX, arrY

Set cht = ActiveSheet.ChartObjects(1).Chart
Set aX = cht.Axes(1)
Set aY = cht.Axes(2)

x0 = aY.Left + aY.Width
y0 = aX.Top + aX.Height

yf = y0 - aY.Top
xf = (aX.Left + aX.Width - x0)

xf = (aX.Left + aX.Width - x0) / (aX.MaximumScale)
yf = (y0 - aY.Top) / aY.MaximumScale

Set sr = cht.SeriesCollection(1)
With sr
arrX = .XValues
arrY = .Values
For i = 1 To .Points.Count
xP = x0 + arrX(i) * xf
yP = y0 - arrY(i) * yf
With .DataLabels(i)
.Left = xP
.Top = yP
End With
Next
End With

End Sub

I know yesterday you said you are working with an MS Graph, I think
it
should work pretty much the same way but try the above in Excel.
First
with
a straight line, then your "difficult" data.

The idea is to create your own co-ord system centred in the
intersection
of
the XY axes, then scale X & Y according to the length to right and
above
the
intersection. Where a chart does not have either of those axes would
use
the plot-area instead.

I'll leave the hard part to you, as to how to work out quite where to
place
those labels on a crowded chart. Actually I'd be interested to see!

Almost forgot to say, for some reason data labels don't seem to
expose
there
width & height properties, hence in this demo top-left label corners
are
placed over points (if it works as intended). I imagine you'll be
able
to
guesstimate sizes depending on text & font, otherwise there's a much
more
long winded way to get a good idea of the size.

Regards,
Peter T



"Phil Stanton" wrote in message
et...
I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the
chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area
is
422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a
point,
what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

for some reason data labels don't seem to expose there
width & height properties,

That was my thinking/experience,

but the OP said: " I know the height and width of the label"

--
Regards,
Tom Ogilvy


"Peter T" wrote:

If for example I want to place a particular label exactly over a point,

I know that's not your real question but if that was, simply
ActiveChart.SeriesCollection(1).DataLabels.Positio n = xlLabelPositionCenter

But if I follow, your real question is how to get the XY coordinates of your
XY points. Have a go with the following

Create an XY embedded chart with one series, keep it simple let each X:Y
make a straight line starting with X:Y = 0:0, 1:1 etc

Option Explicit
Sub test()
Dim i As Long
Dim cht As Chart
Dim sr As Series
Dim aX As Axis, aY As Axis
Dim x0 As Single, y0 As Single, xP As Single, yP As Single
Dim xf As Double, yf As Double
Dim arrX, arrY

Set cht = ActiveSheet.ChartObjects(1).Chart
Set aX = cht.Axes(1)
Set aY = cht.Axes(2)

x0 = aY.Left + aY.Width
y0 = aX.Top + aX.Height

yf = y0 - aY.Top
xf = (aX.Left + aX.Width - x0)

xf = (aX.Left + aX.Width - x0) / (aX.MaximumScale)
yf = (y0 - aY.Top) / aY.MaximumScale

Set sr = cht.SeriesCollection(1)
With sr
arrX = .XValues
arrY = .Values
For i = 1 To .Points.Count
xP = x0 + arrX(i) * xf
yP = y0 - arrY(i) * yf
With .DataLabels(i)
.Left = xP
.Top = yP
End With
Next
End With

End Sub

I know yesterday you said you are working with an MS Graph, I think it
should work pretty much the same way but try the above in Excel. First with
a straight line, then your "difficult" data.

The idea is to create your own co-ord system centred in the intersection of
the XY axes, then scale X & Y according to the length to right and above the
intersection. Where a chart does not have either of those axes would use
the plot-area instead.

I'll leave the hard part to you, as to how to work out quite where to place
those labels on a crowded chart. Actually I'd be interested to see!

Almost forgot to say, for some reason data labels don't seem to expose there
width & height properties, hence in this demo top-left label corners are
placed over points (if it works as intended). I imagine you'll be able to
guesstimate sizes depending on text & font, otherwise there's a much more
long winded way to get a good idea of the size.

Regards,
Peter T



"Phil Stanton" wrote in message
et...
I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the
chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area is 422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a point,
what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

Hello again Tom !

but the OP said: " I know the height and width of the label"

So he did, I wonder how

One way I had in mind was placing the text into an autosize textbox with
same font and with no internal margins, generally works well.

Another way would be to position labels left & right, top & bottom, trapping
their left & top coord's in each position.
eg, .Position = xlLabelPositionLeft
Would learn just about all you need to know about label size as well as the
Point position. A bit of a kludge though.

Regards,
Peter T


"Tom Ogilvy" wrote in message
...
for some reason data labels don't seem to expose there
width & height properties,

That was my thinking/experience,

but the OP said: " I know the height and width of the label"

--
Regards,
Tom Ogilvy


"Peter T" wrote:

If for example I want to place a particular label exactly over a
point,

I know that's not your real question but if that was, simply
ActiveChart.SeriesCollection(1).DataLabels.Positio n =
xlLabelPositionCenter

But if I follow, your real question is how to get the XY coordinates of
your
XY points. Have a go with the following

Create an XY embedded chart with one series, keep it simple let each X:Y
make a straight line starting with X:Y = 0:0, 1:1 etc

Option Explicit
Sub test()
Dim i As Long
Dim cht As Chart
Dim sr As Series
Dim aX As Axis, aY As Axis
Dim x0 As Single, y0 As Single, xP As Single, yP As Single
Dim xf As Double, yf As Double
Dim arrX, arrY

Set cht = ActiveSheet.ChartObjects(1).Chart
Set aX = cht.Axes(1)
Set aY = cht.Axes(2)

x0 = aY.Left + aY.Width
y0 = aX.Top + aX.Height

yf = y0 - aY.Top
xf = (aX.Left + aX.Width - x0)

xf = (aX.Left + aX.Width - x0) / (aX.MaximumScale)
yf = (y0 - aY.Top) / aY.MaximumScale

Set sr = cht.SeriesCollection(1)
With sr
arrX = .XValues
arrY = .Values
For i = 1 To .Points.Count
xP = x0 + arrX(i) * xf
yP = y0 - arrY(i) * yf
With .DataLabels(i)
.Left = xP
.Top = yP
End With
Next
End With

End Sub

I know yesterday you said you are working with an MS Graph, I think it
should work pretty much the same way but try the above in Excel. First
with
a straight line, then your "difficult" data.

The idea is to create your own co-ord system centred in the intersection
of
the XY axes, then scale X & Y according to the length to right and above
the
intersection. Where a chart does not have either of those axes would
use
the plot-area instead.

I'll leave the hard part to you, as to how to work out quite where to
place
those labels on a crowded chart. Actually I'd be interested to see!

Almost forgot to say, for some reason data labels don't seem to expose
there
width & height properties, hence in this demo top-left label corners are
placed over points (if it works as intended). I imagine you'll be able
to
guesstimate sizes depending on text & font, otherwise there's a much
more
long winded way to get a good idea of the size.

Regards,
Peter T



"Phil Stanton" wrote in message
et...
I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the
chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area is
422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a
point,
what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

I think when I was facing that for pie charts, I used an average character
width and threw in a fudge factor. Guess it depends on how accurate one
wants to be.

--
Regards,
Tom Ogilvy




"Peter T" wrote:

Hello again Tom !

but the OP said: " I know the height and width of the label"

So he did, I wonder how

One way I had in mind was placing the text into an autosize textbox with
same font and with no internal margins, generally works well.

Another way would be to position labels left & right, top & bottom, trapping
their left & top coord's in each position.
eg, .Position = xlLabelPositionLeft
Would learn just about all you need to know about label size as well as the
Point position. A bit of a kludge though.

Regards,
Peter T


"Tom Ogilvy" wrote in message
...
for some reason data labels don't seem to expose there
width & height properties,

That was my thinking/experience,

but the OP said: " I know the height and width of the label"

--
Regards,
Tom Ogilvy


"Peter T" wrote:

If for example I want to place a particular label exactly over a
point,

I know that's not your real question but if that was, simply
ActiveChart.SeriesCollection(1).DataLabels.Positio n =
xlLabelPositionCenter

But if I follow, your real question is how to get the XY coordinates of
your
XY points. Have a go with the following

Create an XY embedded chart with one series, keep it simple let each X:Y
make a straight line starting with X:Y = 0:0, 1:1 etc

Option Explicit
Sub test()
Dim i As Long
Dim cht As Chart
Dim sr As Series
Dim aX As Axis, aY As Axis
Dim x0 As Single, y0 As Single, xP As Single, yP As Single
Dim xf As Double, yf As Double
Dim arrX, arrY

Set cht = ActiveSheet.ChartObjects(1).Chart
Set aX = cht.Axes(1)
Set aY = cht.Axes(2)

x0 = aY.Left + aY.Width
y0 = aX.Top + aX.Height

yf = y0 - aY.Top
xf = (aX.Left + aX.Width - x0)

xf = (aX.Left + aX.Width - x0) / (aX.MaximumScale)
yf = (y0 - aY.Top) / aY.MaximumScale

Set sr = cht.SeriesCollection(1)
With sr
arrX = .XValues
arrY = .Values
For i = 1 To .Points.Count
xP = x0 + arrX(i) * xf
yP = y0 - arrY(i) * yf
With .DataLabels(i)
.Left = xP
.Top = yP
End With
Next
End With

End Sub

I know yesterday you said you are working with an MS Graph, I think it
should work pretty much the same way but try the above in Excel. First
with
a straight line, then your "difficult" data.

The idea is to create your own co-ord system centred in the intersection
of
the XY axes, then scale X & Y according to the length to right and above
the
intersection. Where a chart does not have either of those axes would
use
the plot-area instead.

I'll leave the hard part to you, as to how to work out quite where to
place
those labels on a crowded chart. Actually I'd be interested to see!

Almost forgot to say, for some reason data labels don't seem to expose
there
width & height properties, hence in this demo top-left label corners are
placed over points (if it works as intended). I imagine you'll be able
to
guesstimate sizes depending on text & font, otherwise there's a much
more
long winded way to get a good idea of the size.

Regards,
Peter T



"Phil Stanton" wrote in message
et...
I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on the
chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area is
422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a
point,
what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil

Get the height & width of a label by trying to move it as far to the right
and bottom of a chart as it will go (over the edge)

My bit of code is

' Move labels over the edge to the bottom right of the chart.
' This fails, so the new top gives the label height and the new left
gives the label width
ChtLabel.Top = ChtHeight
ChtLabel.Left = ChtWidth

LabelHeight = ChtHeight - ChtLabel.Top
LabelWidth = ChtWidth - ChtLabel.Left

where ChtLabel is the DataLabel and ChtHeight is Chart.Height etc

Hope this is correct

Phil


"Tom Ogilvy" wrote in message
...
I think when I was facing that for pie charts, I used an average character
width and threw in a fudge factor. Guess it depends on how accurate one
wants to be.

--
Regards,
Tom Ogilvy




"Peter T" wrote:

Hello again Tom !

but the OP said: " I know the height and width of the label"

So he did, I wonder how

One way I had in mind was placing the text into an autosize textbox with
same font and with no internal margins, generally works well.

Another way would be to position labels left & right, top & bottom,
trapping
their left & top coord's in each position.
eg, .Position = xlLabelPositionLeft
Would learn just about all you need to know about label size as well as
the
Point position. A bit of a kludge though.

Regards,
Peter T


"Tom Ogilvy" wrote in message
...
for some reason data labels don't seem to expose there
width & height properties,

That was my thinking/experience,

but the OP said: " I know the height and width of the label"

--
Regards,
Tom Ogilvy


"Peter T" wrote:

If for example I want to place a particular label exactly over a
point,

I know that's not your real question but if that was, simply
ActiveChart.SeriesCollection(1).DataLabels.Positio n =
xlLabelPositionCenter

But if I follow, your real question is how to get the XY coordinates
of
your
XY points. Have a go with the following

Create an XY embedded chart with one series, keep it simple let each
X:Y
make a straight line starting with X:Y = 0:0, 1:1 etc

Option Explicit
Sub test()
Dim i As Long
Dim cht As Chart
Dim sr As Series
Dim aX As Axis, aY As Axis
Dim x0 As Single, y0 As Single, xP As Single, yP As Single
Dim xf As Double, yf As Double
Dim arrX, arrY

Set cht = ActiveSheet.ChartObjects(1).Chart
Set aX = cht.Axes(1)
Set aY = cht.Axes(2)

x0 = aY.Left + aY.Width
y0 = aX.Top + aX.Height

yf = y0 - aY.Top
xf = (aX.Left + aX.Width - x0)

xf = (aX.Left + aX.Width - x0) / (aX.MaximumScale)
yf = (y0 - aY.Top) / aY.MaximumScale

Set sr = cht.SeriesCollection(1)
With sr
arrX = .XValues
arrY = .Values
For i = 1 To .Points.Count
xP = x0 + arrX(i) * xf
yP = y0 - arrY(i) * yf
With .DataLabels(i)
.Left = xP
.Top = yP
End With
Next
End With

End Sub

I know yesterday you said you are working with an MS Graph, I think
it
should work pretty much the same way but try the above in Excel.
First
with
a straight line, then your "difficult" data.

The idea is to create your own co-ord system centred in the
intersection
of
the XY axes, then scale X & Y according to the length to right and
above
the
intersection. Where a chart does not have either of those axes would
use
the plot-area instead.

I'll leave the hard part to you, as to how to work out quite where to
place
those labels on a crowded chart. Actually I'd be interested to see!

Almost forgot to say, for some reason data labels don't seem to
expose
there
width & height properties, hence in this demo top-left label corners
are
placed over points (if it works as intended). I imagine you'll be
able
to
guesstimate sizes depending on text & font, otherwise there's a much
more
long winded way to get a good idea of the size.

Regards,
Peter T



"Phil Stanton" wrote in message
et...
I have a scatter chart and I want to be able to position the labels
differently for each point ( depending on the space available on
the
chart)

I know the X & Y co-ordinates of the points in the series.

The chart is 622 point wide and the interior width of the plot area
is
422
point wide. I know the height and width of the label

If for example I want to place a particular label exactly over a
point,
what
is the calculation using the X co-ordinate, widths to give me the
DataLabel.Left value

Thanks for any help

Phil