BeforeDelete key event???

Posted by Beginner Bob on February 22, 2001 2:57 PM

Back for some more help. I have a workbook where I add sheets with a custom built menu. Info is added to the worksheets via userform input, auto sorted alphabetically, then subtotalled with a macro. I would like to have a msgbox pop-up if the user tries to delete anything in the range "A4:H65536" on any sheet, asking the user if he/she would like the whole row deleted or nothing at all (since the subtotal function coupled with the autosort macro screws everything up.) I know I could protect the sheet and put a button in my menu for delete row, but I would like to not have to protect the sheet. I think I would need to put this in the workbook code, since sheets are added at later dates. Can someone help me with this? Thanks in advance.

Posted by David Hawley on February 22, 2001 8:36 PM

Hi Bob

The easiest way to do this is insert a new sheet. Right click on the sheet name tab and select "View Code". Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim Reply As Interior
If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then
Set WatchRange = Range("A4:H65536")
On Error Resume Next
If Not Intersect(Target, WatchRange) Is Nothing Then
Reply = MsgBox("Ok for entire row", vbOKCancel)
If Reply = vbOK Then Target.EntireRow.Delete
End If
End If
End Sub

Now, you can either save the sheet as a Template by moving it to a new Workbook and deleting all other sheets.
Or hide the sheet within your workbook. Either way all you need to do is use the sheet housing the code as as Template for all new sheets your macros create.

Hope this helps

Dave

OzGrid Business Applications

Posted by Beginner Bob on February 26, 2001 2:53 PM

Thanks Dave.

I actually created another small userform (as called by clicking between columns A:H) which displays the row contents in textboxes allowing the user to change them, along with a button on the userform for deleting the entire column. This is working for now, but will probably end up trying your code out. Thanks again. Hope this helps Dave