Macro to protect formulas only

[howard]

I have the following code below to protect formulas on all sheets

 Sub ProtectAll()

    Dim sht As Worksheet
    Dim cel As Range
    Dim twb As Workbook
    
        Set twb = ThisWorkbook
        
        For Each sht In twb.Sheets
        
            For Each cel In sht.UsedRange
                If cel.HasFormula = True Then
                    cel.Locked = True
                Else
                    cel.Locked = False
                End If
            Next cel
            
            sht.Protect "Password"
            
        Next sht

End Sub

However when running the macro, I get a run time error "unable to set the locked property of the range class

the code below is highlighted

  cel.Locked = False

It would be appreciated if someone could assist me

 

[mohadin]

Hi
see If this work for you

Sub ProtectAll()
    Dim sht As Worksheet
    Dim cel As Range
    Dim twb As Workbook
        Set twb = ThisWorkbook
        For Each sht In twb.Sheets
        sht.Unprotect "Password"
            For Each cel In sht.UsedRange
                If cel.HasFormula = True Then
                    cel.Locked = True
                Else
                    cel.Locked = False
                End If
            Next cel
            sht.Protect "Password"
        Next sht
End Sub

 

[howard]

thanks for the help

when running the macro, I still get run time error " get a run time error "unable to set the locked property of the range class"

the code below is highlighted

 cel.Locked = False

 

[mohadin]

It's works fin in here!!!
Tested so many times

 

[mohadin]

Would you try this

Sub ProtectAll()
    Dim sht As Worksheet
    Dim cel As Range
    Dim twb As Workbook
    Set twb = ThisWorkbook
    For Each sht In twb.Sheets
        sht.Unprotect "Password"
        For Each cel In sht.UsedRange
            If cel.HasFormula = True And cel.Locked = False Then
                cel.Locked = True
            Else
                If cel.Locked = True Then
                    cel.Locked = False
                End If
            End If
        Next cel
        sht.Protect "Password"
    Next sht
End Sub

 

[howard]

I still get same error

Not sure what is causing this

 

[howard]

I managed to sort this out using another approach for certain sheets only

 Sub ProtectAll()
Dim i As Long

For i = Sheets("BT1").Index To Sheets("Month").Index - 1
      With Sheets(i)

.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Protect AllowDeletingRows:=True

End With
Next i
End Sub

 

[mohadin]

Ok,
Well done
Sorry I could not help
Be happy

 

[howard]

Thanks for trying to assist. I always appreciate the help