Lock and protect ranges

  • Thread starter Thread starter simon
  • Start date Start date
S

simon

I've adapted some code I found on this group to protect rows that have
text in column A. Here is the code which runs on the Save event:
Dim i As Integer
ActiveSheet.Unprotect
For i = 1 To Range("A65536").End(xlUp).Row
If Cells(i, 1).Value <> "" Then
Cells(i, 1).EntireRow.Locked = True
Else
Cells(i, 1).EntireRow.Locked = False
End If
Next i
ActiveSheet.Protect

Interestingly I discovered that when I only had one row of information
not only was that row protected but also all the rows below it.
However when I inserted an additional row both rows with text in
column A were protected but other rows could be edited as originally
intended.

Can anyone explain why this is happening. It took me some while to
fathom.

Many thanks in anticipation

Simon
 
Can anyone explain why this is happening. It took me some while to
fathom.
Click to expand...


Yes. Yor code works upwards from the last populated row in column A so none
of the rows below that are changed by your code so when you protect the sheet
all cells below your last row of data are locked (The default) and become
protected when you protect the sheet.

Rows above your last row are unlocked if column A is empty and so don't
become protected.

What do you want it to do?

Mike
 
As an afterthought if you want to be able to edit empty rows after execution
of the code it's a single line addition to what you have already

Dim i As Integer
ActiveSheet.Unprotect
ActiveSheet.Cells.Locked = False 'additional line
For i = 1 To Range("A65536").End(xlUp).Row
If Cells(i, 1).Value <> "" Then
Cells(i, 1).EntireRow.Locked = True
Else
Cells(i, 1).EntireRow.Locked = False
End If
Next i
ActiveSheet.Protect
End Sub

Mike
 
In fact you can now dispense with the ELSE statement becuase it will never
execute

Dim i As Integer
ActiveSheet.Unprotect
ActiveSheet.Cells.Locked = False
For i = 1 To Range("A65536").End(xlUp).Row
If Cells(i, 1).Value <> "" Then
Cells(i, 1).EntireRow.Locked = True
End If
Next i
ActiveSheet.Protect

Mike
 
Sub IfTextLock()
With ActiveSheet
.Unprotect
.Columns(1).SpecialCells _
(xlCellTypeConstants, 2).EntireRow.Locked = True
.Protect
End With
End Sub
 
Since your original post said TEXT, I wrote this for that to NOT lock
numbers. If you want to lock ALL cells that are not blanks, simply remove
the

,2
 
Back
Top