Protect lines from deleting

[kubabocz]

Hi guys,

I have decided to move this to a new thread as I think it may be useful for other users as well and it should be easier to find it like that.

I have question related to one macro.

I have a quite big file where rows are blocked (you can not add new line or delete existing one).
On the other hand I have to allow people to add a lines. Adding line has to be always in the line above so it doesn't break the structure and formulas, this is where I use below Macro:

Sub Copy_Highlighted_Row()
[COLOR=#008000][B]' Unprotect worksheet with password[/B][/COLOR]
    ActiveSheet.Unprotect "TEST"
[COLOR=#008000][B]' Copy the entire selected row[/B][/COLOR]
        Selection.EntireRow.Copy
[COLOR=#008000][B]' Insert the copied row in the row above your selection[/B][/COLOR]
            Selection.Insert Shift:=xlUp
[COLOR=#008000][B]' Turn off the "marching ants" dotted line[/B][/COLOR]
        Application.CutCopyMode = False
[COLOR=#008000][B]' Reprotect the worksheet with password[/B][/COLOR]
    ActiveSheet.Unprotect "TEST"
End Sub

Because of the fact that all rows are protected, people can not delete the lines. I would like to let them to delete just some of the lines, this is where I use next Macro

Sub Delete_Highlighted_Row()
[COLOR=#008000][B]' If target row is 12, 23, 24, 31, 39 or 48 then...[/B][/COLOR]
    If ActiveCell.Row = 12 Or ActiveCell.Row = 23 Or ActiveCell.Row = 24 Or ActiveCell.Row = 31 Or ActiveCell.Row = 39 Or ActiveCell.Row = 48 Then
[COLOR=#008000][B]' Give an error stating this row cannot be deleted[/B][/COLOR]
        MsgBox "Row " & ActiveCell.Row & " cannot be deleted"
[COLOR=#008000][B]' Else Unprotect worksheet with password[/B][/COLOR]
            Else: ActiveSheet.Unprotect "TEST"
[COLOR=#008000][B]' Delete the entire selected row[/B][/COLOR]
                Selection.EntireRow.Delete
[COLOR=#008000][B]' Reprotect the worksheet with password[/B][/COLOR]
                    ActiveSheet.Protect "TEST"
    End If
End Sub

The oly problem is that the Macro above is limited to just 43 conditions (which means I can only put 43 different lines that can not be deleted). Every single time when I want to put more the formula is changing to red. I have about 100 lines I want to make sure no one can delete. Is there any way to do that.

Ps. Thank you Fishboy for help with quoted Macros.

 

[Peter_SSs]

You could use something like this. Just be careful to ensure there is a comma (& no spaces) between each row number in the 'Const' line.

Sub Delete_Highlighted_Row()
  Const RowsToProtect As String = "12,23,24,31,39,48," _
                                  & "49,50,58,61,68,72," _
                                  & "79,85"
  
  If InStr(1, "," & RowsToProtect & ",", "," & ActiveCell.Row & ",") Then
    MsgBox "Row " & ActiveCell.Row & " cannot be deleted"
  Else
    ActiveSheet.Unprotect "TEST"
    ActiveCell.EntireRow.Delete
    ActiveSheet.Protect "TEST"
  End If
End Sub

 

[kubabocz]

Thank you very much Peter_SSs,

It works fine untill you delete one line as then the numbers change. It requires something more dynamic.
Fortunately Fishboy helped me as part of different thread and the whole problem is fixed now.