Resizing a Text Box in a Macro

You can pick Page Layout > Arrange > Align > Snap to Grid (Alt+P+AA+P), then manually move/resize shapes to align with a range.

@Mike J
The Tip's original ResizeBox1 macro can be simplified by replacing the following statements
        .Width = rng.Left + rng.Width
        .Height = rng.Top + rng.Height
with these statements
        .Width = rng.Width
        .Height = rng.Height

Here is a simplified version of the Tip's ResizeBox2 macro:

Sub ResizeBox2()
    Dim rRangeToCover As Range
    ' Ensure a text box is selected
    If TypeName(Selection) <> "TextBox" Then
        MsgBox "Text box not selected"
        Exit Sub
    End If
    ' Get the range to cover
    Set rRangeToCover = ActiveSheet.Range("RangeToCover")
    ' Resize the text box
    With Selection
        .Left = rRangeToCover.Left
        .Top = rRangeToCover.Top
        .Width = rRangeToCover.Width
        .Height = rRangeToCover.Height
    End With
End Sub

Here is another version that works with any visible shape (including Notes, Charts, and Pictures) and is perhaps more user-friendly:

Sub CoverRangeWithShape()
    Const myName As String = "CoverRangeWithShape"
    Const OneSecond As Single = 1 / 24 / 3600
    Dim shp As Shape, rng As Range, msg As String, ans As Variant
    If Not (TypeOf ActiveSheet Is Worksheet) Then Exit Sub
    If ActiveSheet.Shapes.Count = 0 Then Exit Sub
    For Each shp In ActiveSheet.Shapes
        If shp.Visible Then
            shp.Select
            shp.TopLeftCell.Show
            Application.Wait (Now + OneSecond)
            msg = shp.Name & " is selected. " _
                & "Do you want to cover a range with this shape?"
            ans = MsgBox(msg, vbYesNoCancel, myName)
            If ans <> vbNo Then Exit For
        End If
    Next shp
    If ans <> vbYes Then Exit Sub
    msg = "Specify the range address or range name to cover. " _
        & vbLf & "You can use the mouse (or similar device)."
    ans = Range(shp.TopLeftCell, shp.BottomRightCell).Address
    On Error Resume Next
        Set rng = Application.InputBox(msg, myName, ans, Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    rng.Cells(1).Show
    With shp
        .Left = rng.Left
        .Top = rng.Top
        .Width = rng.Width
        .Height = rng.Height
    End With
End Sub

@J.Woolley
Thank you for the corrections.
I couldn't get the second macro to work either, but I didn't understand the statement that caused the error, so thought it may be me; especially as Debug/Compile_VBA_Project did not find any problems. Clearly it can't catch them all!

@Mike J
The following statements in macro ResizeBox1
        .Width = rng.Left + rng.Width
        .Height = rng.Top + rng.Height
should be replaced with these corrected statements
        .Width = rng.Left + rng.Width - .Left
        .Height = rng.Top + rng.Height - .Top
The Tip's ResizeBox2 macro doesn't work at all. To fix it, replace the following statement
    Set l_rRangeToCover = _
      ActiveSheet.Range(.Names("RangeToCover").RefersToRange.Value)
with this corrected statement
    Set l_rRangeToCover = _
      ActiveSheet.Range("RangeToCover")

The first macro doesn't seem to work correctly.
If top left is set to A1 and bottom right to E5, then the text box correctly occupies 5 rows and 5 columns from A1:E5. But if the top left is C3 and bottom right E5, the text box still occupies 5 rows and 5 columns - from C3:G7