Macro to protect formulas only

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,974
Office Version
  1. 2019
Platform
  1. Windows
I have the following code below to protect formulas on all sheets


Code:
 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

Code:
  cel.Locked = False

It would be appreciated if someone could assist me
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,092
Office Version
  1. 2013
Platform
  1. Windows
Hi
see If this work for you
VBA Code:
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,974
Office Version
  1. 2019
Platform
  1. Windows
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

Code:
 cel.Locked = False
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,092
Office Version
  1. 2013
Platform
  1. Windows
It's works fin in here!!!
Tested so many times
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,092
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Would you try this
VBA Code:
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,974
Office Version
  1. 2019
Platform
  1. Windows
I still get same error

Not sure what is causing this
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,974
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Code:
 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

Well-known Member
Joined
Mar 22, 2015
Messages
1,092
Office Version
  1. 2013
Platform
  1. Windows
Ok,
Well done
Sorry I could not help
Be happy
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,974
Office Version
  1. 2019
Platform
  1. Windows
Thanks for trying to assist. I always appreciate the help
 

Forum statistics

Threads
1,137,125
Messages
5,679,755
Members
419,855
Latest member
Eddier32

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top