Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect and lock cell in Pivottable | Excel Worksheet Functions | |||
how to only protect/lock cell formats? | Excel Discussion (Misc queries) | |||
Protect or Lock Column | Excel Programming | |||
Lock and protect cells without protect the sheet | Excel Programming | |||
Protect and Lock Problem SOLVED!! | Excel Programming |