Disable row delete in a worksheet (not all worksheets in theworkbook)

[Lucas Reece]

Is it possible to prevent deleting of rows in a worksheet within a
workbook? e.g. I have Sheet1, Sheet2, Sheet3 and Sheet4 in the
workbook but only want to prevent deletion of rows in Sheet2.

Can someone offer a solution to this using VBA?

Many thanks.

 

[john]

Excel does not provide direct functionality that enables
you to "trap" a row that has been deleted.
This code is stored in the Thisworkbook code module
and should prevent rows from being deleted in a sheet named sheet2.

And of course, it only works if user enables macros.

Hope helpful

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.Name = "Sheet2" Then

If Target.Address = Target.EntireRow.Address Then

With Application

.EnableEvents = False

.Undo

msg = MsgBox("Deleting Rows Not Permitted", 16, "WARNING")

.EnableEvents = True

End With

Else

Exit Sub

End If
End If

End Sub

 

[Lucas Reece]

Superb! Thanks John.

Excel does not provide direct functionality that enables
you to "trap" a row that has been deleted.
This code is stored in the Thisworkbook code module
and should prevent rows from being deleted in a sheet named sheet2.

And of course, it only works if user enables macros.

Hope helpful

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Sh.Name = "Sheet2" Then

        If Target.Address = Target.EntireRow.Address Then

            With Application

                .EnableEvents = False

                .Undo

                msg = MsgBox("Deleting Rows Not Permitted", 16, "WARNING")

                .EnableEvents = True

            End With

        Else

            Exit Sub

        End If
    End If

End Sub

 

[JENC]

Is it possible to limit this to one particular row on a worksheet?

John