none
Position a Textbox Relative to a Bar on a Chart RRS feed

  • General discussion

  • I'm trying to move some textboxes show that they sit immediately above the corresponding bars on a chart. To do so, I'm using the top of each point as a reference (and then subtracting a fixed value from that value). This isn't working. There are very strange values for some of these "pt.top" values. Some of them are negative. These are stacked bar charts, and I'm trying to place the textbox above the top of the stack.

    Can anyone help me to find the correct top for these textboxes? Thanks.

        Set sr = cht.Chart.SeriesCollection(1)
    
            For Each pt In sr.Points
            
                'add the label
                Set tb = ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, 141.75, 66.75, 72, 72)
                tb.Name = "tb" & i
            
                'populate, position, and size the label
                With tb
                    'populate with the value of the bar
                    .TextFrame2.TextRange.Characters.Text = i 'pt.DataLabel.Caption
                    
                    'move the label above the bar and position it
                    .top = pt.top - 15
                    .left = pt.left
                    .width = pt.width
                    .height = 15
                                  
                End With
    
                i = i + 1
                 
            Next pt

    Saturday, August 11, 2012 8:40 PM

All replies

  • You need to set the height of the object before setting the top because the default height as it was creates is too high to fit into the first postion and therefore it moves it up so that the object will fit on the chart.

    Added with Edit: Width needs to be a fixed width. I have realized now that the pt.width is variable.

            For Each pt In sr.Points
                i = i + 1
                'add the label
                Set tb = ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, 141.75, 66.75, 72, 72)
                tb.Name = "tb" & i
           
                'populate, position, and size the label
                With tb
                    'populate with the value of the bar
                    .TextFrame2.TextRange.Characters.Text = i 'pt.DataLabel.Caption
                   
                    'move the label above the bar and position it
                    .Height = 15    '<----****** before setting Top
                    .Top = pt.Top - 15
                    .Left = pt.Left
                    .Width = 30      '<----***** Needs to be fixed width; pt.Width is variable
                End With
            Next pt


    Regards, OssieMac



    • Edited by OssieMac Sunday, August 12, 2012 4:55 AM
    Sunday, August 12, 2012 4:38 AM
  • Do you need to use code?

    Can you not add another data series and apply data labels, or is the text to long for a data label given you can not control dimensions of a data label?


    Cheers,

    Andy
    www.andypope.info

    Sunday, August 12, 2012 11:00 AM