マクロ内のテキストボックスのサイズ変更（Microsoft Excel）

Sub ResizeBox1()

Dim sTL As String     Dim sBR As String     Dim rng As Range

' Change top-left and bottom-right addresses as desired     sTL = "A1"

sBR = "M40"



' Ensure a text box is selected     If TypeName(Selection) <> "TextBox" Then         MsgBox "Text box not selected"

Exit Sub     End If

With Selection         Set rng = ActiveSheet.Range(sTL)

.Top = rng.Top         .Left = rng.Left         Set rng = ActiveSheet.Range(sBR)

.Width = rng.Left + rng.Width         .Height = rng.Top + rng.Height     End With     Set rng = Nothing End Sub

Sub ResizeBox2()

Dim l_rRangeToCover As Range     Dim l_rLowerRight 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 l_rRangeToCover = _       ActiveSheet.Range(Names("RangeToCover").RefersToRange.Value)



' Get its lower right cell     Set l_rLowerRight = _       l_rRangeToCover.Cells( _       l_rRangeToCover.Rows.Count, _       l_rRangeToCover.Columns.Count)



' Resize the text box     With Selection         .Left = l_rRangeToCover.Left         .Top = l_rRangeToCover.Top         .Width = l_rLowerRight.Left + l_rLowerRight.Width - .Left         .Height = l_rLowerRight.Top + l_rLowerRight.Height - .Top     End With End Sub