CODEWITHSUNDEEP
excelvba
Geographos
Geographos

Reputation: 1496

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?

Upvotes: 0

Views: 162

Answers (1)

Dariusz Sok&#243;ł
Dariusz Sok&#243;ł

Reputation: 167

Before you protect the sheet, define which area of the sheet should be affected by the protection.

First select and right click the corner and uncheck the 'Locked' checkbox for all cells in the protection section.

enter image description here

enter image description here

Then select the five rows you'd like to lock and check the 'Locked' checkbox.

enter image description here

Now when you protect your sheet it should only affect the five rows you have chosen to be locked.

Upvotes: 1

Related Questions