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

madvogue29

New Member
Joined
Aug 28, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
L

Legacy 456155

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

1599816152627.png


1599816206228.png
 

madvogue29

New Member
Joined
Aug 28, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Thanks DataLuver! that did the trick!! I hadnt even thought of it that way ! Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,127,528
Messages
5,625,339
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top