Excel VBA - protect worksheet but leave some cells unprotected

[Harry Flashman]

I have a worksheet that I want to protect, but leave some cells unprotected. Normally what Is right-click the cells I wish to leave unprotected, choose format/protection and then untick locked.

I have a macro that clears the unprotected range:

Sub ClearPasteRange()
Dim ws As Worksheet
Dim MyRng As Range
Set ws = Worksheets("Chart")
ws.Unprotect
Set MyRng = ws.Range("MyPasteRange")
MyRng.Clear
Calculate
ws.Protect
End Sub

The thing is the range I wished to leave unprotected MyPasteRange ends up being protected again after the macro is run. That is the cells properties change to being locked again.

It doesn't happen if I manually unprotect/protect the worksheet. It only happens when I run the macro.
Is this usual? Where am I going wrong.

Edit: I have just realized something silly. I don't need to unprotect the sheet in my macro. doh!
Still I am curious about this behaviour from the macro.

 

[Harry Flashman]

Okay I figured it out. The Clear method clears all of the cells properties, including its protection. Thus I need to use .ClearContents instead.

 

[Harry Flashman]

Actually that was wrong too. I also wanted to clear the formats. This code works:

Sub ClearPasteRange()
Dim ws As Worksheet
Dim MyRng As Range
Set ws = Worksheets("Chart")
ws.Unprotect
Set MyRng = ws.Range("MyPasteRange")
With MyRng
    .Clear
    .Locked = False
End With
    
Calculate
ws.Protect

End Sub

 

[ChrisKingston]

Okay I figured it out. The Clear method clears all of the cells properties, including its protection. Thus I need to use .ClearContents instead.

Years Old but great to find the answer. Thanks