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](https://stackoverflow.com/questions/3037400/how-to-lock-the-data-in-a-cell-in-excel-using-vba) — Mech, Sep 21 '20 at 06:41
Read up on [SpecialCells](https://learn.microsoft.com/en-us/office/vba/api/excel.range.specialcells) with `XlCellType` as `xlCellTypeFormulas` and then lock only those cells. — Siddharth Rout, Sep 21 '20 at 06:45
I’ve tried the .Cells.SpecialCells(xlCellTypeFormulas).Locked = True but it says No cells were found. Any idea how to fix this? — vt-0307, Sep 21 '20 at 06:48
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, Sep 21 '20 at 06:51
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, Sep 21 '20 at 06:58
Try the code for a single worksheet, being sure that there are formulas on it. It certainly there are no formulas in a specific worksheet. You must put the line between `On Error Resume Next` and `On Error GoTo 0` and do the locking only if the range has been created... — FaneDuru, Sep 21 '20 at 07:01

Siddharth Rout · Accepted Answer · 2020-09-21T07:20:07.953

5

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

edited Sep 21 '20 at 07:20

answered Sep 21 '20 at 07:01

Siddharth Rout

147,039
17
206
250

thanks. This kind of works but any idea how to code such that when I protect, users only can select unlocked cells? Currently, users can select all cells even if the sheet is protected. – vt-0307 Sep 21 '20 at 07:06
Just add `.EnableSelection = xlUnlockedCells` after `.Protect myPass`. I have updated the post above. You may have to refresh the page to see it – Siddharth Rout Sep 21 '20 at 07:08
Sorry, last question. what if I’d like to keep the first sheet unprotected? – vt-0307 Sep 21 '20 at 07:11
After `With ws` add `If Not ws.Name = "RelevantSheetName" Then` and put the rest of the code inside that, – Siddharth Rout Sep 21 '20 at 07:14
So would this work if let’s say I have 3 sheets that I want to keep unprotected such as Navigation, Template and Details. so I’ll just code ``` If Not ws.Name = “Navigation”, “Template”, “Details Then ``` – vt-0307 Sep 21 '20 at 07:17
No. If you have multiple sheets then it is better to use select case. much easier.. – Siddharth Rout Sep 21 '20 at 07:18
Sorry, still quite new to VBA. May I know how that works? – vt-0307 Sep 21 '20 at 07:21
1

I have already updated the answer. refresh to see it – Siddharth Rout Sep 21 '20 at 07:23
@SiddharthRout will this work if I want to protect cells that display date and time after data was entered? – zvzej Mar 11 '22 at 18:38

How can I lock cells with formulas?

1 Answers1