Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Insert Text box next to axis value (Excel 2000 >)

    Hi,
    In the attached WB, the macro adds a text box next to the maximum Y-Axis value. If the plot area size is changed...(ie. activate plot area and size the top handle down to half way) and then re-run the macro, the text box is still added to the top, and not next to the high value.

    Is there a way to add the text box always relative to the highest value on the axis?
    Tx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert Text box next to axis value (Excel 2000 >)

    Sub AddLabel()
    Dim cht As Chart
    Set cht = ActiveSheet.ChartObjects("Chart 1").Chart
    With cht.Shapes.AddTextbox(msoTextOrientationHorizontal , _
    20, cht.PlotArea.Top, 39.75, 16.5)
    .TextFrame.Characters.Text = "High"
    End With
    Range("A1").Select
    End Sub

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert Text box next to axis value (Excel 2000 >)

    Many tx again for the prompt and concise reply!
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert Text box next to axis value (Excel 2000 >)

    Hans,
    I'm trying to modify the code so the text box is added to the top of a series in the chart. The following code is still not working <img src=/S/sad.gif border=0 alt=sad width=15 height=15>. Could you assist again please. Tx
    <pre>Sub AddLabelFix()
    Dim cht As Chart
    Set cht = ActiveSheet.ChartObjects("Chart 1").Chart
    With cht.Shapes.AddTextbox(msoTextOrientationHorizontal , _
    20, cht.SeriesCollection("High"), 39.75, 16.5)
    .TextFrame.Characters.Text = "High"
    End With
    Range("A1").Select
    End Sub
    </pre>

    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert Text box next to axis value (Excel 2000 >)

    That's quite different. A chart series does not have a Top property - it depends on the data in the series and on the axis properties.

    You'd have to take the highest value (use the worksheet function Max) in the series, retrieve the MaximumScale and MinimumScale of the value axis, the Top and Height of the PlotArea, and combine all this to calculate the "top" of the series. Good luck! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Insert Text box next to axis value (Excel 2000 >)

    <P ID="edit" class=small>(Edited by sdckapr on 26-Jan-06 07:53. Added workbook with code removed)</P>It may be easier (If I understand what you are after) to use "DataLabels" rather than a macro:

    No coding required
    It is "live"
    It is a lot simpler (excel does all the "work")

    [I added some formulas underneath your data to calculate the labels.]
    Steve

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert Text box next to axis value (Excel 2000 >)

    Tx Steve,
    How did you get the data label there? I did something and it was replaced by the numerical value. Now I cannot get the "High" above the series again. I feel rather dumb!!!

    Tx
    Regards,
    Rudi

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Insert Text box next to axis value (Excel 2000 >)

    This is one of my "pet peeves" with excel. You can not directly put text into datalabels (the Lotus 123 in the DOS days did this directly and it is a feature i used a lot)

    You can do it three primary ways:
    1) you can select each label individually and enter in the formula bar an equal (=) then either select the cell or enter the cell reference.
    2) You can write a macor to do it for you.
    3) you can use Rob Bovey's <img src=/S/free.gif border=0 alt=free width=30 height=15> addin XY Chart Labeler (which I highly recommend)

    Rob's addin adds the feature that excel should have explicitly included.

    Steve

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert Text box next to axis value (Excel 2000 >)

    Gratefully tx!
    Regards,
    Rudi

Posting Permissions

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