Erlandsen Data Consulting

Protect all cells containing formulas in a worksheet

2009-12-17 Worksheets 0 173

The procedure below can be used to protect all cells containing formulas in a worksheet.

Sub ProtectFormulasInWS(ws As Worksheet)
' ws must be unprotected before running this procedure
' protect ws afterwards for the protection to take effect
Dim objRange As Range
    If ws Is Nothing Then Exit Sub
    
    With ws
        If .ProtectContents Then Exit Sub
        
        .Cells.Locked = False
        .Cells.FormulaHidden = False
        On Error Resume Next
        Set objRange = .UsedRange.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
        If Not objRange Is Nothing Then
            With objRange
                .Locked = True
                .FormulaHidden = True
            End With
            Set objRange = Nothing
        End If
    End With
End Sub

Sub ExampleProtectFormulasInWS()
    ActiveSheet.Unprotect
    ProtectFormulasInWS ActiveSheet
    ActiveSheet.Protect
End Sub