Excel VBA Protect Sheet without Locking all cells

Question

In an Excel Spreadsheet, I'm attempting to safeguard the headers. I did this by selecting the full sheet, going to the cell properties page, and unchecking the "locked" box. I then checked "locked" and only chose the first row.

My macros work well the first time, but when I try to run them again, I get errors about the sheets being locked, and when I go back and check my sheets, EVERY cell is now locked once more. There is no VBA code in my programme that directs me to lock any cells. To safeguard the sheets, I'm running the following macro:

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 the 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 would prefer not to have to enter this additional code when I don't think I should have to because I want to understand WHY all of my cells are locking. Is there a flaw in Excel that makes it set the locked attribute, or am I overlooking something in this code that locks them by default?

Answer 1

If the problem is the 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