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
Holger Gerths wrote:
> 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.
>
>
> "Bernard Liengme" <(E-Mail Removed)> schrieb im Newsbeitrag
> news:(E-Mail Removed)...
>
>>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
>>
>>--
>>Bernard Liengme
>>www.stfx.ca/people/bliengme
>>remove CAPS in e-mail address
>>
>>
>>"Holger Gerths" <(E-Mail Removed)> wrote in message
>>news:On2MX%(E-Mail Removed)...
>>
>>>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.
>>>
>>>(E-Mail Removed)
>>>
>>>
>>>
>>
>>
>
>
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
Similar Threads
