Results 1 to 2 of 2
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Springfield, Ohio, USA
    Thanked 1 Time in 1 Post

    VBA, which point? (XL 2K)

    Assume p is an Excel.Point in an XY Scatter chart, then p.Parent is a Series, so I can parse p.Parent.Formula and extract the X-range and/or the Y-range, but how can I determine which cell in the range is p?

    Restating the problem differently, if ser is a Series and pt is a Point in ser, how can I determine an index, ind, so that s.XValues(ind) and s.Values(ind) are the coordinates of p? Do I have to search for p.DataLabel.Text or is there a straight-forward way?

    TIA --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Weert, Limburg, Netherlands
    Thanked 0 Times in 0 Posts

    Re: VBA, which point? (XL 2K)

    If the chart has no datalabels, you might do this:

    <pre> With ActiveChart.SeriesCollection(1)
    .ApplyDataLabels Type:=xlDataLabelsShowValue, _
    AutoText:=True, LegendKey:=False
    MsgBox ActiveChart.SeriesCollection(1).Points(2).DataLabe l.Text
    .ApplyDataLabels Type:=xlDataLabelsShowLabel, _
    AutoText:=True, LegendKey:=False
    MsgBox ActiveChart.SeriesCollection(1).Points(2).DataLabe l.Text
    End With

    Of course you could check the point in question for its datalabel, store that and then change it to do what I suggested.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts