Lock and protect ranges
 

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

Lock and protect ranges
 

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


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

"simon" wrote:

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

Lock and protect ranges
 

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

"Mike H" wrote:

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


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

"simon" wrote:

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

Lock and protect ranges
 

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

"Mike H" wrote:

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

"Mike H" wrote:

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


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

"simon" wrote:

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

Lock and protect ranges
 

Sub IfTextLock()
With ActiveSheet
.Unprotect
.Columns(1).SpecialCells _
(xlCellTypeConstants, 2).EntireRow.Locked = True
.Protect
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"simon" wrote in message
...
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

Lock and protect ranges
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Sub IfTextLock()
With ActiveSheet
.Unprotect
.Columns(1).SpecialCells _
(xlCellTypeConstants, 2).EntireRow.Locked = True
.Protect
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"simon" wrote in message
...
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