Position Textbox on Chart Relative to Chart Axes

[JCReynolds79]

Hi all,

I have a chart, which has a textbox on it, which is part of the chart, i.e. when I move the chart, the textbox goes with it.

I want to be able to position the text box relative to the chart axes if at all possible.

So far I have only worked out that I can position the textbox relative to the whole chart window. i.e. .Top = 0 and .Left = 0 will put the text box at the very top left of the whole chart 'window'.

I would like to move the textbox relative to the axes, drivin by vb code so as the shape of the chart changes, the text box can be driven to be in the right place (a visual thing).

Thanks in advance,

Jon

 

[SkipVought]

Hi,

Check out the PlotArea object properties, like Top, Left, InsideTop and InsideLeft.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MarkWalsh]

What type of chart are you using? Bar chart? Pie Chart?

If you are trying to keep the textbox with a value in the chart, I assume this may be a very difficult task, as I don't think it is possible through code to get the positions or dimensions of the objects (bars, pie slices, etc) that represent the data.

 

[JCReynolds79]

Thanks for the replies.

It is currently an XY scatter chart, which I am using to draw lines, just to simply represent a simple system. It dynamically displays the results of calculations.

What would just make it more useful is if I could position text boxes relative to the chart series to act as 'dimensions', but it only will work if I can keep following the moving series.

Thanks so far.

Jon

 

[MarkWalsh]

A quick try of looping through the chart's 'Shapes' collection did not produce any results for me. It apparently doesn't refer to the actual shapes that make up the chart data points. I don't know if what you want to do is possible, you can check to see if there are any other members of the Chart class that might be what you want, but I didn't see anything.
My knowledge of the Excel model is somewhat limited (I mostly access it through PowerPoint to edit datea in embedded charts). If what you want is possible, perhaps someone else may chime in with better information.

 

[SkipVought]

If you have DataLable objects for your Point Objects in your series object, you can get the Top, & Left properties.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JCReynolds79]

Thanks all for the responses.

Skip, even the data labels' top and left properties are relative to the overall chart window, not the datapoint it is attached to, which is a shame as that would have been nice.

Never mind, guess I need to find another way.

Cheers.

 

[SkipVought]

Yes, and that's the point. You can use the points as references to position other objects.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JCReynolds79]

SkipVought,

Ah ok I see what you're saying... any pointers as to how I would do that? I have limited knowledge with VB... If I can record a macro and learn then fine, but not sure how I would do that to find out how to position 'something' relative to a datalabel.

Sound promising

Thanks.

 

[SkipVought]

This positions textbox named tbTest 10 points above and to the left of the first data label in the first embedded chart on Sheet1.

Sub test()
    Dim sr As Series, pt As Point, i As Integer
    i = 1
    With Sheets("Sheet1").ChartObjects(1).Chart
        For Each sr In .SeriesCollection
            For Each pt In sr.Points
                If pt.HasDataLabel Then
                    If i = 1 Then
                        .Shapes("tbTest").Top = pt.DataLabel.Top - 10
                        .Shapes("tbTest").Left = pt.DataLabel.Left - 10
                    End If
                End If
                i = i + 1
            Next
        Next
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I tweeked the code a bit.

Suppose that your series has no data label. The code can temporarily add a dl for the purpose of positioning the text box and then remove it. This one positions on the THIRD point...

Sub test()
    Dim sr As Series, pt As Point, i As Integer, bRestore As Boolean
    i = 1
    With Sheets("Sheet1").ChartObjects(1).Chart
        For Each sr In .SeriesCollection
            For Each pt In sr.Points
                If i = 3 Then
                    bRestore = False
                    If Not pt.HasDataLabel Then
                        pt.ApplyDataLabels _
                                AutoText:=True, _
                                ShowValue:=True
                        bRestore = True
                    End If
                    .Shapes("tbTest").Top = pt.DataLabel.Top - 10
                    .Shapes("tbTest").Left = pt.DataLabel.Left - 10
                    If bRestore Then
                        pt.ApplyDataLabels _
                                AutoText:=True, _
                                ShowValue:=False
                    End If
                End If
                i = i + 1
            Next
        Next
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JCReynolds79]

Hey Thanks Skip!

Hopefully I will interogate this and get it working. Top man, thanks.

Jon