Locking all form controls in Excel file

[N Prakash]

Hi,

I am currently working on 51 spreadsheets each having various form controls. Especially my list box (from Form controls) resizes and also moves to different cells. I want to lock all sheets with various form controls. The option from "Format control >> Properties" Don't move or size with cells is not working. I found following code in the forum, and this is returning error "Compilation error, Invalid use of Me keyword". I changed from TextBox to ListBox and also tried changing locked to Enabled, but it is not working. Can anyone please help to resolve the issue or provide any new VBA code.

The Code I am using is:

Private Sub Form_Current()

Dim ctrl As Control

For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox Then
ctrl.Locked = True
End If
Next

End Sub

to unlock

CODE​

Private Sub UnlockButton_Click()

Dim ctrl As Control

For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox Then
ctrl.Locked = False
End If
Next

End Sub

 

[DanteAmor]

Review the following codes to change the Placement property of form controls.
Check which one you need.

Note: Works with all workbook sheets

Sub controls_xlFreeFloating()
  Dim sh As Worksheet
  Dim ctrl As Object
 
  For Each sh In Sheets
    For Each ctrl In sh.DrawingObjects
      If TypeName(ctrl) = "ListBox" Then
        ctrl.Placement = xlFreeFloating       'Don't move or size with cells
        ctrl.Locked = msoTrue
      End If
    Next
  Next
End Sub

Sub controls_xlMove()
  Dim sh As Worksheet
  Dim ctrl As Object
 
  For Each sh In Sheets
    For Each ctrl In sh.DrawingObjects
      If TypeName(ctrl) = "ListBox" Then
        ctrl.Placement = xlMove               'Object is moved with the cells
        ctrl.Locked = msoFalse
      End If
    Next
  Next
End Sub

Sub controls_xlMoveAndSize()
  Dim sh As Worksheet
  Dim ctrl As Object
  Dim col() As Variant
  Dim n As Long
 
  Application.ScreenUpdating = False
  For Each sh In Sheets
    sh.Select
    n = 0
    For Each ctrl In sh.DrawingObjects
      If TypeName(ctrl) = "ListBox" Then
        ReDim Preserve col(n)
        col(n) = ctrl.Name
        n = n + 1
      End If
    Next
   
    'Works only with more than one control (listbox) on the sheet
    If n > 0 Then
      sh.Shapes.Range(col).Select
      Selection.Placement = xlMoveAndSize     'Object is moved and sized with the cells
      Selection.Locked = msoFalse
      Range("A1").Select
    End If
  Next
  Sheets(1).Select
  Application.ScreenUpdating = True
End Sub