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.

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

Superb! Thanks John.

On 8 Oct, 16:08, john <j...@discussions.microsoft.com> 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.

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 <j...@discussions.microsoft.com> 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.
>
>