How To Lock Cells In Excel Vba

How to Lock Cells in Excel VBA

To lock cells in Excel using VBA (Visual Basic for Applications), you can use the “Locked” property of the Range object. The “Locked” property allows you to specify whether a cell or range should be locked or unlocked.

Example:

Suppose we want to lock cells A1 to A5 in the worksheet named “Sheet1”. Here’s how you can do it:

    Sub LockCellsExample()
      Dim ws As Worksheet
      Dim rng As Range
      
      ' Set the worksheet and range variables
      Set ws = ThisWorkbook.Worksheets("Sheet1")
      Set rng = ws.Range("A1:A5")
      
      ' Lock the range
      rng.Locked = True
      
      ' Protect the worksheet
      ws.Protect
      
    End Sub
  

In the above example, we first set the worksheet variable “ws” to reference the worksheet named “Sheet1”. Then, we set the range variable “rng” to refer to cells A1 to A5. We then use the “Locked” property to lock the range by setting it to True. Finally, we protect the worksheet using the “Protect” method.

Once the worksheet is protected, the locked cells will be protected from being edited or modified. Users will only be able to select and view the locked cells, but they won’t be able to make any changes.

Note that the “Locked” property only takes effect when the worksheet is protected. If the worksheet is not protected, the locked cells can still be edited or modified.