CODEWITHSUNDEEP
vbaexcelmacros
Tobias Glaus
Tobias Glaus

Reputation: 3628

Excel: Position text box via macro

I got a Command Button in my Excel file, which adds a new text box at a specific location. Now i got a problem. I'd like to add the text box automatically under the previous one. So like on my screenshot. I created "Testeintrag" manually and "Titelname hier eingeben" gets created when I hit the + on the top. I placed the box manually with

ActiveSheet.Shapes.AddTextbox(msoTextOrientationUpward, 932, 270, 27, _
    150).Select

But when I add another one, it just over my previous one, but I'd like that it creates the next one under my previous one, and so on. How can I do this?

The Text box "Titelname hier eingeben" gets created when I hit the + on the top.

Thats my whole macro:

Private Sub CommandButton1_Click()
ActiveSheet.Shapes.AddTextbox(msoTextOrientationUpward, 932, 270, 27, _
    150).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
    "Titelname hier eingeben"
End Sub

Upvotes: 0

Views: 8015

Answers (2)

Martin Dreher
Martin Dreher

Reputation: 1564

Edit2: Updated code according to new requirement.

Option Explicit

Sub addayetnothertextbox()

    Dim mybox

    Dim top, left, height, width, margin
    margin = 20

    With ThisWorkbook.ActiveSheet.Shapes

        left = .Item(.Count).left
        top = .Item(.Count).top + .Item(.Count - 1).height + margin
        width = .Item(.Count).width
        height = .Item(.Count).height

        Set mybox = .addtextbox(msoTextOrientationUpward, left, top, width, height)

    End With

End Sub

Upvotes: 2

Tobias Glaus
Tobias Glaus

Reputation: 3628

Since that method was to complicated for me, I created a simple alternative.

 Private Sub CommandButton1_Click()
Dim Left As Double, Top As Double
With ActiveSheet
Left = ActiveCell.Left
Top = ActiveCell.Top
ActiveSheet.Shapes.AddTextbox(msoTextOrientationUpward, Left, Top, 27, _
    190).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
    "Titelname hier eingeben"
End With
End Sub

So I have to select a cell and then hit that "Add"-Button. Then it adds a text box with the size 190x25 at the location where the active cell is.

Upvotes: 0

Related Questions