Datapoint position

H

Holger Gerths

Hi,

does anybody know how to find the exact position of a datapoint or a column
in a (stacked) column chart?
The purpose is to place a vertical line (exactly) between to columns.

Thx, Holger.

[email protected]
 
B

Bernard Liengme

Why not draw the line?
If you check the box the place x-axis labels between columns, the exact
centre is marked for you
Use View|Toolbars to show Drawing toolbar
Click on chart to select it (otherwise the line will not be part of the
chart)
Click the line tool on Drawing toolbar; draw lien. Hold down Shift to help
get vertical line

Best wishes
 
H

Holger Gerths

Argggh! Of course I do speak about drawing the line with VBA!
The Problem is that a datapoint object has no VBA-readable position
properties, only the datalabel has and this is more than unexact!
:-(
Holger.
 
H

Holger Gerths

An answer would be more pleasant!
But I notice that this is a problem which cannot be solved on the fly.
:-(((
 
A

Andy Pope

Hi Holer,

If you really must have VBA code to draw a line then maybe this will help.
Assumes a chartobject on a worksheet. The chart is a Stacked column with
at least 4 categories. It should also have a line already embedded in
the chart.

The SetVline routine allows you to specify the left and right category
that the line is to be positioned between.

'--<Code Start>-----
Sub PlaceVLine()
SetVline ActiveSheet.ChartObjects(1).Chart, 3, 4
End Sub
Sub SetVline(MyChart As Chart, LeftCategory As Integer, RightCategory As
Integer)
'
'Position a vertical line in between Left and Right Category
'
Dim intCatCount As Integer
Dim shpVLine As Shape
Dim sngStep As Single

With MyChart
Set shpVLine = .Shapes(1)
intCatCount = .SeriesCollection(1).Points.Count
If LeftCategory > RightCategory Then Exit Sub
If LeftCategory < 1 Or RightCategory < 1 Then Exit Sub
If LeftCategory > intCatCount Or RightCategory > intCatCount
Then Exit Sub
If .Axes(xlCategory).AxisBetweenCategories Then
sngStep = .PlotArea.InsideWidth / intCatCount
shpVLine.Left = .PlotArea.InsideLeft + (((LeftCategory -
0.5) + ((RightCategory - LeftCategory) / 2)) * sngStep)
Else
sngStep = .PlotArea.InsideWidth / (intCatCount - 1)
shpVLine.Left = .PlotArea.InsideLeft + (((LeftCategory - 1)
+ ((RightCategory - LeftCategory) / 2)) * sngStep)
End If
shpVLine.Width = shpVLine.Width
shpVLine.Top = .PlotArea.InsideTop
shpVLine.Height = .PlotArea.InsideHeight
End With
End Sub
'--<Code End>-----

Cheers
Andy
 
J

Jon Peltier

Holger -

I have linked you to an answer that has been posted many times. So many
times that I built a web site for it and many of the other answers.

Using a helper series means you don't need to do all the hard algebra to
position your line, and if the scale or size of the plot area changes,
the line is more likely to still be relevant after the change. Andy's
macro might have to be run after each change.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
H

Holger Gerths

Thanks , this was a really useful answer!


Jon Peltier said:
Holger -

I have linked you to an answer that has been posted many times. So many
times that I built a web site for it and many of the other answers.

Using a helper series means you don't need to do all the hard algebra to
position your line, and if the scale or size of the plot area changes,
the line is more likely to still be relevant after the change. Andy's
macro might have to be run after each change.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Click to expand...
 
Top