CODEWITHSUNDEEP
vbaexcelprotected
M.Tailleur
M.Tailleur

Reputation: 113

Protect Sheet from inserting rows only in a specific range

I am trying to find a way to allow the user to insert rows but only in a specific range when the sheet is protected. For example, I don't want the user to be able to insert a row between the rows "1" and "2" (because it will make my macros do funny things), but I want him to be able to insert a row everywhere else.

The following code allows the user to insert rows everywhere in the sheet, but that is not exactly what I want:

    ActiveSheet.Protect Password:="qwerty", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:=True

Can somebody help me with this issue ?

Upvotes: 2

Views: 2781

Answers (2)

Srikanta Gouda
Srikanta Gouda

Reputation: 521

You can use the below code on Worksheet Selection Change event.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row = 1 Or Target.Row = 2 Then
        'MsgBox "Check"
        ActiveSheet.Protect Password:="qwerty", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingRows:=False, AllowDeletingRows:=True
    Else
        ActiveSheet.Unprotect Password:="qwerty"
    End If
End Sub

Upvotes: 2

B Slater
B Slater

Reputation: 340

There's no way to prevent the user from inserting rows only in specific ranges, you could lock those cells and prevent selection of locked cells so the user doesn't select cells in the top two rows which might prevent them from accidentally doing it?

You could also lock row insertion and set UserInterfaceOnly:=True and deal with any row insertion through a macro, cancelling the procedure if they're trying to insert a row in the wrong place.

Is it a necessity that the users can insert rows throughout the worksheet?

Upvotes: 2

Related Questions