Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif and Sumif with refrence value a Cell on another Sheet in theWorkbook | Excel Discussion (Misc queries) | |||
Disable Worksheet Delete Warning | Excel Programming | |||
VBA to disable "Delete...." on shortcut menu of worksheet tab? | Excel Discussion (Misc queries) | |||
Disable Worksheet Delete Dialog | Excel Programming | |||
Disable Add/Delete/Move Worksheets | Excel Programming |