How To Lock And Unlock Cells In Excel Using Vba

How to Lock and Unlock Cells in Excel Using VBA

In order to lock and unlock cells in Excel using VBA, you can follow these steps:

1. Open the Excel workbook that you want to work with.
2. Press “ALT + F11” to open the Visual Basic for Applications (VBA) editor.
3. In the VBA editor, go to “Insert” and choose “Module” to create a new module.
4. In the new module, you can write VBA code to lock and unlock cells based on your requirements.

Example 1: Lock Specific Cells

Suppose you want to lock cells A1 and B1 and unlock cell C1 in the active sheet. You can use the following VBA code:

    Sub LockUnlockCells()
        With ActiveSheet
            .Range("A1:B1").Locked = True
            .Range("C1").Locked = False
        End With
    End Sub
  

In this example, the “.Locked” property is used to lock or unlock the cells. Setting it to “True” locks the cells, while setting it to “False” unlocks the cells.

Example 2: Lock All Cells Except Header Row

If you want to lock all cells in a sheet, except for the header row (first row), you can use the following VBA code:

    Sub LockUnlockCells()
        With ActiveSheet
            .Cells.Locked = True
            .Rows(1).Locked = False
        End With
    End Sub
  

In this example, the “.Cells.Locked” property is used to lock all cells, and then the “.Rows(1).Locked” property is used to unlock the header row.

Applying the Locking/Unlocking

After writing the VBA code to lock and unlock cells, you can run the code by pressing “F5” in the VBA editor. This will execute the code and apply the specified locking/unlocking to the cells in the active sheet.