How can I lock cells with formulas?

Question

My excel on default have all columns locked when I select protect sheet.

I’d like to use a VBA code where I only lock cells with formulas (and only allow users to select unlocked cells) while looping through every worksheet that I have in the workbook. This is the code that I currently have.

Sub LockSheets()
    Dim ws As Worksheet
    For Each ws In Worksheets
        With ws
            .Unprotect
            .Cells.Locked = False
            .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
            .Protect
        End With
    Next ws
End Sub

Does this answer your question? How to Lock the data in a cell in excel using vba — Mech
Read up on SpecialCells with XlCellType as xlCellTypeFormulas and then lock only those cells. — Siddharth Rout
I’ve tried the .Cells.SpecialCells(xlCellTypeFormulas).Locked = True but it says No cells were found. Any idea how to fix this? — vt-0307
That is becuase that particular sheet did not have cells with formulas? Aso when using SpecialCells you have to do proper handling. Post what you have tried and we can take it from there — Siddharth Rout
Updated my code. I have certain sheets where there are no formulas at all. I only want to protect sheets that have formulas. — vt-0307

Siddharth Rout Siddharth Rout · Accepted Answer · 2020-09-21T07:01:26

Is this what you are trying? I have commented the code so you should not have a problem understanding it. But if you do, then simply ask.

Option Explicit

'~~> Change this to the relevant password
Const myPass As String = "MyPassword"

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    
    '~~> Loop through worksheets
    For Each ws In ThisWorkbook.Sheets
        With ws
            Select Case .Name
            '~~> Ignore these sheets
            Case "Navigation", "Template", "Details"
            Case Else
                .Unprotect myPass
                
                .Cells.Locked = False
                
                '~~> Set your range which contains forulas
                On Error Resume Next
                Set rng = .Cells.SpecialCells(xlCellTypeFormulas)
                On Error GoTo 0
                
                '~~> If found then set them locked. This is required
                '~~> because some sheet(s) may not have formulas
                If Not rng Is Nothing Then rng.Locked = True
                
                '~~> Reset to nothing to prevent false results
                Set rng = Nothing
                
                '~~> Protect sheet
                .Protect myPass
                
                '~~> Allow selectiong of only unlocked cells
                .EnableSelection = xlUnlockedCells
            End Select
        End With
    Next ws
End Sub

How can I lock cells with formulas?

1 Answers