Excel VBA Protect Sheet without Locking all cells

Question

I am trying to protect the headers in an Excel Spreadsheet. In order to do so, I selected the entire sheet, went to cell properties, and unchecked "locked". Then, I selected the first row only and checked "locked".

My macros run fine once, then on running again I get errors related to the sheets being locked, and when I go back and check my sheets, now ALL the cells are locked again. I do not have any VBA code specifying to lock any cells. I have this macro running to protect the sheets:

Public Sub ProtectSheet(Optional sheetname As String)
    
    Dim thisSheet As Worksheet
    'This is to protect sheet from userinterface, but not from macros
    If IsMissing(sheetname) Then sheetname = ""
    If sheetname = "" Then
        Set thisSheet = ActiveWorkbook.ActiveSheet
    Else
        Set thisSheet = ActiveWorkbook.Worksheets(sheetname)
    End If
    
    thisSheet.Protect UserInterfaceOnly:=False, Contents:=True, DrawingObjects:=True, Scenarios:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowFormattingCells:=True, AllowInsertingHyperlinks:=True, AllowSorting:=True, AllowFiltering:=True

End Sub

I created VBA code as follows to unprotect the sheet, select all and unlock, then lock the first row, then protect. It works when I do this, but I don't understand why I have to.

Public Sub ProtectSheet(Optional sheetname As String)
    
    Dim thisSheet As Worksheet
    'This is to protect sheet from userinterface, but not from macros
    If IsMissing(sheetname) Then sheetname = ""
    If sheetname = "" Then
        Set thisSheet = ActiveWorkbook.ActiveSheet
    Else
        Set thisSheet = ActiveWorkbook.Worksheets(sheetname)
    End If
    
    thisSheet.Unprotect
    thisSheet.Cells.Locked = False
    thisSheet.Rows(1).Locked = True
    thisSheet.Protect UserInterfaceOnly:=False, Contents:=True, DrawingObjects:=True, Scenarios:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowFormattingCells:=True, AllowInsertingHyperlinks:=True, AllowSorting:=True, AllowFiltering:=True

End Sub

I want to understand WHY all my cells are locking and would prefer not to have to add this extra code in when I don't believe I should have to. Is there a bug in Excel causing the locked property to get set, or I am missing something in this code that is locking them automatically?

Can't reproduce what you're reporting. Likely there's something specific missing here — Tim Williams
I'm not sure what it could be. I searched my entire project and there are no instances of .lock anywhere (prior to the updated code to fix the issue) - are there any other VBA properties that could indirectly affect the .lock property? — Trashman
Are you using Clear ? stackoverflow.com/questions/31007163/… — Tim Williams
@TimWilliams I assume from the question that Clear sets the "Locked" bit. Yes, I am using "Clear" so that explains the issue. Are there any alternatives to clear? I need to clear everything from the cells EXCEPT I need to maintain the unlocked state. — Trashman

Tim Williams Tim Williams · Accepted Answer · 2021-06-01T23:06:05

If the problem is use of Clear then consider creating a separate sub to manage that, and call it instead of Clear.

Sub ClearButUnlocked(rng As Range)
    with rng
        .clear
        .cells.locked=false
    end with
end sub

...assuming rng only has one lock state and is not a mix of locked/unlocked cells

Excel VBA Protect Sheet without Locking all cells

1 Answers