Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text boxes on line graph (Excel 2000 SR-1)

    I'm using Excel to chart a line graph of the closing values of the S&P 500 each day. At the end of each month I have the opportunity to move my investments around among the funds of my 401K. At the end of each month I manually place a small text box containing a single character on the chart next to the data point for that day (see attached gif image) to indicate whether I bought ([img]/forums/images/smilies/cool.gif[/img], sold (S), or held (H) my stock positions. This works OK except that:

    1. Adding the text boxes is labor intensive (they also contain hyperlinks and screen tip pop-up text). Is there any way to automatically place a text box on a chart in a precise location using VBA or other Excel facilities? I could add a column to the data sheet to indicate my actions (Bought, Sold, Held) if that would help.

    2. The text boxes aren't really attached to the chart. As I add new data values each day, the line graph compresses to fit within the chart space, but the text boxes remain in fixed positions. This necessitates shifting each text box to the left slightly. Is there any way to force the text boxes to move with the data points on the line?

    Thanks in advance,
    Attached Images Attached Images

  2. #2
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text boxes on line graph (Excel 2000 SR-1)

    Thank you. The data label add-in worked well even on a line graph. It's a bit of a trade-off though. Data labels provide automatic positioning, but text boxes allow for hyperlinks and screen tips which are immensely useful. Thanks again.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text boxes on line graph (Excel 2000 SR-1)

    Maybe the best way is to use data labels. Add an extra column and put the single character B, S or H next to the data-point you want to label. Then use an data label add-in that you can download free of charge: Have a look at this MVP Page for a free add-in to add labels to data points (XYChartlabeler.exe). This add-in requires your graph to be an XY scatter plot.

    Edited Mar 13th 2004 to update link

Posting Permissions

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