How to protect specific cells in a sheet using VBA?

Protecting specific cells in an Excel sheet using VBA involves a few key steps:

Unlock all cells: By default, all cells in an Excel worksheet are locked, but this locking takes effect only when the sheet is protected. To protect specific cells, you need to unlock all cells first.
Lock the specific cells: After unlocking all cells, you can then lock the cells you want to protect.
Protect the sheet: Finally, you protect the sheet so that only the locked cells are protected from editing.

Here’s how you can do this using VBA:

Sub ProtectSpecificCells()

    Dim ws As Worksheet
    Dim rangeToLock As Range
    
    ' Set your worksheet name
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Define the range of cells you want to protect
    ' For example, to protect cells A1 to B2, you would use:
    Set rangeToLock = ws.Range("A1:B2")
    
    ' Unlock all cells in the sheet
    ws.Cells.Locked = False
    
    ' Lock specific cells
    rangeToLock.Locked = True
    
    ' Protect the worksheet
    ' You can also specify a password like "Password" for the protection
    ws.Protect Password:="", AllowFormattingCells:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True
    
    MsgBox "The selected cells are now protected.", vbInformation

End Sub

Explanation:

Unlock All Cells: `ws.Cells.Locked = False` unlocks all cells in the specified worksheet.
Lock Specific Cells: `rangeToLock.Locked = True` locks the range you specified for protection.
Protect Worksheet: `ws.Protect` applies protection to the worksheet, and you can provide a password if desired. Additionally, there are options to allow certain actions even when the sheet is protected (like AllowFormattingCells).

Notes:

Adjust `Sheet1` to the name of the sheet you want to protect.
Modify the range `A1:B2` in `ws.Range(“A1:B2”)` to the range you want to protect.
If you want to prevent the user from formatting cells, inserting rows or columns, etc., you can adjust the parameters in the `ws.Protect` method accordingly.

This VBA script effectively locks only the selected range of cells and keeps the rest of the spreadsheet editable, assuming those cells are unlocked.

facebooklinkedin