(Solved) – Excel VBA Partial sheet locking and no option for copy, paste, undo

(solved)-–-excel-vba-partial-sheet-locking-and-no-option-for-copy,-paste,-undo

I have got a cell locking problem in Excel.

So far I used this code for all my worksheets

 Private Sub Worksheet_SelectionChange(ByVal target As Range)
 Const pw As String = "Secret"

 Dim rFormulaCheck As Range

 On Error Resume Next

 With target
 .Parent.Unprotect pw
 .Locked = False
 .FormulaHidden = False

If .Cells.Count = 1 Then
If .HasFormula Then
.Locked = True
.FormulaHidden = True
.Parent.Protect pw, , , , 1
End If

ElseIf .Count > 1 And .Count < 5 Then

 With .SpecialCells(xlCellTypeFormulas)
 .Locked = True
 .FormulaHidden = True
 .Parent.Protect pw, , , , 1
 End With

 End If

 End With

 On Error GoTo 0
 End Sub

where I locked first top 5 rows.
I can do edits on the lower ones, but there is no option to copy the stuff from some cell to the next sheet (to the similar place). The undo option (Crtl Z) also is not working.
How can I modify this code?

I also tried to set some conditions here:

enter image description here

But it didn't help.

It looks like this first 5 rows locking has impact on entire worksheet.
Is it possible to confine it to the first 5 rows only with keeping the normal edit, copy & paste conditions for the rest of this sheet?

Leave a Reply

Your email address will not be published. Required fields are marked *