In Excel, VBA - How can we lock resizing of a rectangle

Question

I have an excel sheet that contains two rectangles and text in other cells. I need to allow users to only edit the text in the rectangle. They should not be able to change the size of the object. Applying lock on the rectangle locks the object as well as the text. Does anyone know how I can achieve this?

Answer 1

Why not create two objects, one being a rectangle that is locked, and one being a text box that is not locked? This is really simplistic, but a possible answer.

Another idea would be to have the rectangle equal a set cell, and let them enter their text in the cell and it would transfer over even when the rectangle is locked.

Answer 2

As far as I am aware Excel does not accommodate Events for shapes and so there is no simple way of detecting a change in a shape size and then resizing the shape.

It is possible to emulate what you are asking for by using a workaround.

Imagine you have two rectangles on your spreadsheet called 'Rectangle 1 and 'Rectangle 2'. When a user finishes updating the text in any given box they must then click the spreadsheet to move out of 'edit' mode for the shape. You can detect this using the Workbook_SheetSelectionChange event.

The following module allows you to set the size of the rectangles as constants and will resize the rectangles accordingly:

Const Rect1Height As Integer = 50
Const Rect1Width As Integer = 200

Const Rect2Height As Integer = 50
Const Rect2Width As Integer = 200

Sub SetRectangleSize()

Dim Rect1 As Shape
Dim Rect2 As Shape

Set Rect1 = ActiveSheet.Shapes("Rectangle 1")
Set Rect2 = ActiveSheet.Shapes("Rectangle 2")

Rect1.Height = Rect1Height
Rect1.Width = Rect1Width
Rect2.Height = Rect1Height
Rect2.Width = Rect1Width

End Sub

Now all you need to do is to call this sub from a workbook level event:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
SetRectangleSize
End Sub

Each time a user updates the text in one of the rectangles they will click back on the spreadsheet and the event is fired, resulting in the rectangles being sized according to the constant height and width parameters that you have defined.