CODEWITHSUNDEEP
vbaexcelms-office
JackTheFruit
JackTheFruit

Reputation: 110

Position TextBox in Chart in Excel to the top / left -4

I have a chart in excel. Inside this chart there is a textbox. I want to position this textbox to the upper left corner of the chart.

When I do this manually and read out the properties .Top and .Left I get -4 as a response.

If I try setting these two properties to -4 the textbox goes to positions 0 and not -4. Why cant I position the texbox to the upper left corner of the chart? Is here some outer margin of the textbox or an inner margin of the chart / chartarea?

Here is some code to test it with any chart, in which you inserted a textbox:

Sub TextBoxPositionUpperLeftCorner()
    Dim cho As ChartObject
    Dim sh As Shape

    For Each cho In ActiveSheet.ChartObjects
        For Each sh In cho.Chart.Shapes

            'reading out the position
            Debug.Print "Top: " & sh.Top
            Debug.Print "Left: " & sh.Left

            'setting the position
            sh.Top = -4
            sh.Left = -4

        Next
    Next
End Sub

Upvotes: 1

Views: 1066

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Seems like you can't set Top/Left to negative numbers.

But you can increment the position to achieve that:

Sub TextBoxPositionUpperLeftCorner()
Dim cho As ChartObject
Dim sh As Shape

For Each cho In ActiveSheet.ChartObjects
    For Each sh In cho.Chart.Shapes

        'reading out the position
        Debug.Print "Top: " & sh.Top
        Debug.Print "Left: " & sh.Left

        sh.IncrementTop -(sh.Top + 4)
        sh.IncrementLeft -(sh.Left + 4)

         'reading out the position
        Debug.Print "Top: " & sh.Top
        Debug.Print "Left: " & sh.Left

    Next
Next

EDIT: after a little more testing, the most negative you can go even with this approach is -4/-4.

Upvotes: 3

Related Questions