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

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.

Disable row delete in a worksheet (not all worksheets in the workb
 

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
--
jb


"Lucas Reece" wrote:

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.

Disable row delete in a worksheet (not all worksheets in theworkb
 

Superb! Thanks John.

On 8 Oct, 16:08, john wrote:
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
--
jb

"Lucas Reece" wrote:
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.

Disable row delete in a worksheet (not all worksheets in the w
 

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

John

"Lucas Reece" wrote:

Superb! Thanks John.

On 8 Oct, 16:08, john wrote:
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
--
jb

"Lucas Reece" wrote:
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.