How do I create a textbox in excel that fills a specific range?

Question

Using vba, I'd like to be able to add it and make it sit over a specific range, like A2:H8, for example. Is that possible?

I know that I could alternatively just merge a bunch of cells and make a text box that way, but I was hoping for something that would be easier for the user to move around if they'd like to.

Right now I'm just using a generic add function, and am trying to get it in the area I want it to be. However, the entire page is going to change due to user input, so depending on what they do, I'll need it to be in a different place. If I tied it to a specific range, it could always be in the perfect place, no matter what the user does.

Here's my current little code snippet:

Sub Macro1()

    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 153.75, 88.5, 509.25, 272.25).Select

    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "hello hello hello" & Chr(13) & "" & Chr(13) & "hello" & Chr(13) & "hi" & Chr(13) & "" & Chr(13) & "hello"

    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 18).ParagraphFormat.FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 18).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(19, 1).ParagraphFormat.FirstLineIndent = 0
End Sub

Answer 1

Consider this:

Sub CoverRange()
    Dim r As Range
    Dim L As Long, T As Long, W As Long, H As Long
    Set r = Range("A2:H8")
    L = r.Left
    T = r.Top
    W = r.Width
    H = r.Height
    With ActiveSheet.Shapes
        .AddTextbox(msoTextOrientationHorizontal, L, T, W, H).Select
    End With
End Sub