none
Protecting a newly edited row RRS feed

  • Question

  • Good Morning

    Is there a way that a row or rows in a workbook can be protected when it has been edited (Only the newly edited rows to be protected).

    Some of the cells in the row are already protected, it's just the unprotected ones that need protecting when cells in the row have been typed in.

    This would only be a particular set of sheets, not all sheets in the workbook.

    The protection would probably take place when the user clicks save.

    Many thanks

    Luke

    Monday, September 4, 2017 10:26 AM

Answers

  • Hi Luke Sykes,
    It seems I mistook something in last reply. Sorry for that.
    I think waht you want is open the workbook, and the edit thes unprotected cell.
    After saving this document, these edited but not protected cells's entire rows will be protected, right?
    I think my second reply could do this for you. However, if you want to set Locked property, you have to unprotect the worksheet. You could unprotect the worksheets in WorkBook.Open event.
    Besides, if you don't want these worksheets are unprotected when you are editing the workbook, you could use an array to record sheet name and row index. When saving the workbook, you could iterate through the array and use sheet name to unprotect the sheet and then use rowindex to set the row's cells locked property and then protect the sheet.
    If you use the code since the workbook is blank, any cell with a value on that sheet would be protected.
    Best Regards,
    Terry
    • Marked as answer by Luke Sykes Monday, September 11, 2017 10:41 AM
    Friday, September 8, 2017 10:59 AM

All replies

  • Hi Luke Sykes,

    You could use WorkSheet.Change event to set Locked property for cells in the entire row of the edited cell.

    Here is the example

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    ActiveSheet.Unprotect
    Target.EntireRow.Cells.Locked = True
    ActiveSheet.Protect
    End Sub

    Best Regards,

    Terry

    Tuesday, September 5, 2017 10:05 AM
  • Thank you Terry

    I have tried that and it seems to lock the row when you have typed in the single cell so you can't fill in further cells along the row, rather than lock the row when you click save...


    • Edited by Luke Sykes Tuesday, September 5, 2017 3:34 PM
    Tuesday, September 5, 2017 12:29 PM
  • Hi Luke Sykes,

    You could use Change event to set Locked property and Workbook.BeforeSave event to protect the sheet.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "Sheet1" Then Target.EntireRow.Cells.Locked = True End If End Sub

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) For Each Sh In ActiveWorkbook.Worksheets If Sh.Name = "Sheet1" Then Sh.Protect End If Next Sh End Sub

    Best Regards,

    Terry

    Wednesday, September 6, 2017 7:28 AM
  • Will that protect the whole sheet or just the newly edited row?
    Wednesday, September 6, 2017 8:06 AM
  • Hi Luke Sykes,
    It will protect cell which's locked property is true. So, not just newly edited row, previous protected range could also be protected. If you don't want to protect previous protected range, you could set their locked property to false.
    Here is the example removing protecting in Workbook.Open event.
    Private Sub Workbook_Open()
    For Each Sh In ActiveWorkbook.Worksheets
    If Sh.Name = "Sheet1" Then
    Sh.Unprotect
    Sh.Cells.Locked = False
    End If
    Next Sh
    End Sub
    Best Regards,
    Terry
    Thursday, September 7, 2017 8:02 AM
  • So does 'True' mean any cell with a value on that sheet will be locked?
    Thursday, September 7, 2017 9:08 AM
  • Hi Luke Sykes,
    It seems I mistook something in last reply. Sorry for that.
    I think waht you want is open the workbook, and the edit thes unprotected cell.
    After saving this document, these edited but not protected cells's entire rows will be protected, right?
    I think my second reply could do this for you. However, if you want to set Locked property, you have to unprotect the worksheet. You could unprotect the worksheets in WorkBook.Open event.
    Besides, if you don't want these worksheets are unprotected when you are editing the workbook, you could use an array to record sheet name and row index. When saving the workbook, you could iterate through the array and use sheet name to unprotect the sheet and then use rowindex to set the row's cells locked property and then protect the sheet.
    If you use the code since the workbook is blank, any cell with a value on that sheet would be protected.
    Best Regards,
    Terry
    • Marked as answer by Luke Sykes Monday, September 11, 2017 10:41 AM
    Friday, September 8, 2017 10:59 AM
  • Thank you Terry

    I will have a look at your solution later

    Monday, September 11, 2017 10:41 AM
  • Hi Terry

    I have just managed to get back to this, work has been crazy.

    Thank you so much for your help on this you are being very helpful.

    Your explanation above is correct, only protect the newly edited cells, and keep previously protected cells protected when you click save.

    Originally I was thinking of protecting the entire row but I expect only protecting newly edited cells when you click save is probably simpler, or if not it definitely fits the criteria of what I am trying to achieve.

    The only problem is, if the worksheet or workbook is unprotected when you first open the workbook doesn't that defeat the object of protecting them in the first place? or am I misunderstanding your comment?

    I have tried both of your codes, as mentioned before, the first one locks the cell as soon as you have typed in it so if you make a mistake before you save it you cant change it.

    The second example just doesn't seem to work or I'm employing it wrong. I copied that to the relevant project for that sheet and changed the sheet name to the name of the sheet, is that correct?

    Thanks Again

    Luke

    Friday, October 6, 2017 1:21 PM
  • Hi Luke Sykes,

    You could use Change event to set Locked property and Workbook.BeforeSave event to protect the sheet.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "Sheet1" Then Target.EntireRow.Cells.Locked = True End If End Sub

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) For Each Sh In ActiveWorkbook.Worksheets If Sh.Name = "Sheet1" Then Sh.Protect End If Next Sh End Sub

    Best Regards,

    Terry

    Hi Luke,

    >>The second example just doesn't seem to work or I'm employing it wrong.

    Did you mean code in this reply?

    If so, it should be add in ThisWorkBook module instead of some specific Sheet module.

    If not, please share your current file so I could try to reproduce your issue. Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 9, 2017 7:01 AM