Stop user from resizing text box or trigger script when textbox is changed.

[madvogue29]

Hi, is there way to stop user from resizing a text box ??
I have this script that brings it back to the pre determined size but is there a way to trigger it every time it is modified.

Ideally I would want this to be dynamic where it does that for all textboxes on the sheet ( if not, i understand)

Thanks in advance

Sub ResizeBox1()
    Dim sTL As String
    Dim sBR As String
    Dim rng As Range

    ' Change top-left and bottom-right addresses as desired
    sTL = "P3"
    sBR = "d15"

    ' Ensure a text box is selected
   ActiveSheet.Shapes.Range(Array("TextBox 1")).Select

    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

 

[Legacy 456155]

You may be able to use a protection scheme. For textboxes, you can exclusively lock its props but leave text unlocked.

 

[madvogue29]

Thanks DataLuver! that did the trick!! I hadnt even thought of it that way ! Cheers