Excel doesn’t provide events for the deletion of rows and columns. There are two methods that you can use to determine if a user deletes a row, and they are described here. If you want to detect column deletion, the process is the same, but some of the details change.
Monitored Public Variable
The first method counts the number of rows used in the spreadsheet, then recounts whenever something is changed. It uses the Worksheet_Change event which fires whenever the user changes any cell in the worksheet including row deletion. Start by creating a public variable in a standard module:
This will hold the number of rows used in the spreadsheet before the change. We’ll compare it to the number of rows after the change to determine if one has been deleted. In the worksheet’s class module, set up an activate event and a change event.
glOldRows = Me.UsedRange.Rows.Count
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Me.UsedRange.Rows.Count < glOldRows Then
MsgBox “Row deleted”
End If
glOldRows = Me.UsedRange.Rows.Count
End Sub
Use the dropdown boxes at the top of the code window to insert the Sub and End Sub statements. The activate event sets the initial value of the public variable whenever the sheet is activated. In the change event, the current number of rows are counted and compared to the public variable to determine if there are fewer.
One downside to this method is that you can’t prevent the deletion, only know that it happened. There’s also a lot of overhead associated with this method. The change event is fired every time something on the sheet is changed. You could work for days or years on this worksheet changing cells without deleting a row. All those precious computer cycles wasted.
Custom Class Module
The second method attempts to capture all the ways that a user can delete a row. The danger of this method is that you may miss a way. I can only see two ways to delete a row: the Delete control on the Row right click menu and the Delete control under the Edit menu. I don’t know of any keyboard shortcuts to delete an entire row.
To use this method, we’ll create a custom class module with two variable; one for each of the controls. We will then monitor the click events of those commandbar controls to determine if the user clicked them. Start by creating a custom class module (Insert > Class Module) . Rename it to CCbarEvents in the Properties Window (F4).
Next create a global variable to hold your custom class module – create this in a standard module:
and use the workbook’s open event to initialize the class module whenever the workbook is opened:
Set gclsCbarEvents = New CCbarEvents
End Sub
Now the class module will be “live” as long as the public variable is in scope, i.e. for as long as the workbook is open. Next we have to put some stuff in the class module that does some work.
Private WithEvents mCellDelButton As CommandBarButton
Private Sub Class_Initialize()
Set mRowDelButton = Application.CommandBars.FindControl(, 293)
Set mCellDelButton = Application.CommandBars.FindControl(, 478)
End Sub
Private Sub mCellDelButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
If TypeName(Selection) = “Range” Then
If Selection.Address = Selection.EntireRow.Address Then
MsgBox Selection.Rows.Count & ” Row” & _
IIf(Selection.Rows.Count = 1, “”, “s”) & ” deleted.”
End If
End If
End Sub
Private Sub mRowDelButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
MsgBox Selection.Rows.Count & ” Row” & _
IIf(Selection.Rows.Count = 1, “”, “s”) & ” deleted.”
End Sub
I declare two variables using the WithEvents keyword to expose the events to our class module. The variables are assigned to the appropriate commandbar controls in the class’ initialize event. I found the IDs of the commandbar controls by going to the immediate window and typing:
293
?application.CommandBars(1).Controls(“&Edit”).controls(“&Delete…”).id
478
Note that the caption for Edit > Delete changes if a row or column is selected instead of a cell. The class’ initialize event is fired when I establish the class in the Workbook_Open event. Next I create the click events for the CommandBarButton variables. For mCellDelButton, I have to first make sure that an entire row (or rows) is selected because this command can be used to delete columns and non-row/column ranges, as well as a bunch of other stuff. I make sure that what is selected is a Range and that the address of the range is the same as the entire row(s).
For mRowDelButton, I know that a whole row is selected, because you can’t show that menu otherwise. The action in each procedure is a message box, but you’ll likely need something more substantive. Don’t forget that you can set the CancelDefault argument of these event procedures to True to prevent the deletion from happening.
If I missed a way that a user can delete a row, or you have a better method for detecting row deletions, please leave a comment.
Control+- (i.e. control+minus key) deletes when an entire row is selected (e.g. with control+space).
I was about to ask if it works with ctrl minus, but I assume it would not as it is looking at menus used.
is there any way to have a class look at keyboard combos (ctrl + x, ctrl + shift + x, etc.)
just confirmed, that doesnt catch ctrl + minus
I think you could catch the Ctrl – or Ctrl + shortcuts using Application.OnKey().
Dang, that makes things more complicated. (BTW, shift+space selects a row). If I use OnKey to capture that shortcut, I have to account for all the other things a user might want to delete. I can live with that, but I can’t figure out how to let the user delete a non-row range. In the UI it asks which way to shift the cells. In code,
doesn’t ask, but rather assumes based on the shape of the range. How do I reproduce the UI experience in code? Anyone?
well could you not checl on ctrl minus of the selection is actualy a row or not ?
that should probably work
Dick, I always customize my standard toolbar by adding the insert/delete row/column buttons, so I’ll have 2 controls with ID 293. I don’t think your code will catch both (?).
This seems to catch them all, including ones in submenus. To test, I put one on the Edit/Fill menu – it caught it twice, once from Edit, once from Fill. I aslo note though that if somebody has more than one copy of the control on a toolbar (why, I don’t know) this will only catch the first:
Sub test()
Dim cbar As CommandBar
Dim mRowDelButton As CommandBarButton
For Each cbar In Application.CommandBars
Set mRowDelButton = cbar.FindControl(ID:=293, recursive:=True)
If Not mRowDelButton Is Nothing Then
‘in case it’s in a sub-menu – index will error
On Error Resume Next
Debug.Print mRowDelButton.Parent.Index, mRowDelButton.Parent.Name, mRowDelButton.Caption
If Err.Description = “Method ‘Index’ of object ‘CommandBar’ failed” Then
Debug.Print mRowDelButton.Parent.ID, mRowDelButton.Parent.Name, mRowDelButton.Caption
End If
End If
Next cbar
End Sub
Just to make it more interesting, in the immediate window, I notice that there are two row menus:
389 Fill Delete &Rows
4 Formatting Delete &Rows
38 Row- &Delete
41 Row- &Delete
389 Fill Delete &Rows
I also note that if there is more than one version
Application.Dialogs(xlDialogEditDelete).Show
Here’s what I have:
If TypeName(Selection) = “Range” Then
If Selection.Address = Selection.EntireRow.Address Then
Selection.Delete
MsgBox Selection.Rows.Count & ” Row” & _
IIf(Selection.Rows.Count = 1, “”, “s”) & ” deleted.”
Else
Selection.Delete
End If
End If
End Sub
I don’t know how to get a return value from the
in order to pass the correct argument to
.
Depends on your definition of “DELETE”, but if you Cut a row, and then Insert Cut Cells onto a different sheet, you’ve blanked out a row…
My earlier comment seems to be in moderation limbo. I’ll try to repeat it:
When I run your code to find the 293 control it only finds one instance – I always put it on my Formatting toolbar as well, and it only finds that one.
The code below finds all instances of 293, I think, including ones on submenus. To test, I put a 293 on the Edit – Fill menu. This code finds it twice, once from the Edit menu, once from Fill.
Interestingly (I’ve seen this before but forgotten about it) it finds 2 Row menus, both with control 293. Also, this code won’t find a second copy of 293 on the same toolbar (No, I don’t do that :))
Sub test()
Dim cbar As CommandBar
Dim mRowDelButton As CommandBarButton
For Each cbar In Application.CommandBars
Set mRowDelButton = cbar.FindControl(ID:=293, recursive:=True)
If Not mRowDelButton Is Nothing Then
‘in case it’s in a sub-menu – Index will error
On Error Resume Next
Debug.Print mRowDelButton.Parent.Index, mRowDelButton.Parent.Name, mRowDelButton.Caption
If Err.Description = “Method ‘Index’ of object ‘CommandBar’ failed” Then
Debug.Print mRowDelButton.Parent.ID, mRowDelButton.Parent.Name, mRowDelButton.Caption
End If
End If
Next cbar
End Sub
Immediate Window:
389 Fill Delete &Rows
4 Formatting Delete &Rows
38 Row &Delete
41 Row &Delete
389 Fill Delete &Rows
Doug: It doesn’t seem to matter how many 293’s I have – the event still fires for all of them. Did you experience something different?
Dick, I completely misunderstood how it worked. You’re absolutely right and it works. Thanks.
I have noticed that sometimes instead of 293, Delete is 292, although I’ve never figured out the pattern.
Dick, I apologize for not actually running your code before critiquing it – I could have at least saved myself some embarrasment. Now that I have run it, I realize just how poorly I understand classes.
To wit, when I read the help for FindControl, it says “If the CommandBars collection contains two or more controls that fit the search criteria, FindControl returns the first control that’s found.” Also, when I add form buttons to a class, I have to add them one at a time (I think).
So can you explain to a simpleton like me how the Initialize event in your class refers to all the 293 controls?
In Excel 2003, if you try to count the rows in the used range, it prevents Application.Undo from working. But if you have a named range on the worksheet, you can access its Address property without preventing the Undo.
Private Sub Worksheet_Change(ByVal Target As Range)
‘Prevents inserting or deleting rows or columns
Static adr As String
Dim str As String
Dim rg As Range
Set rg = Range(“A1:IU65535?)
str = “PartOfWorksheet_BeingWatched”
Application.EnableEvents = False
If adr = “” Then
rg.Name = str
adr = Range(str).Address
ElseIf Range(str).Address adr Then
Application.Undo
MsgBox “You are not permitted to insert/delete columns or rows”, vbExclamation
Else
adr = rg.Address
ActiveWorkbook.Names(str).RefersTo = “='” & ActiveSheet.Name & “‘!” & adr
End If
Application.EnableEvents = True
End Sub
Dick
I suggest that you dont use the UsedRange.Rows.Count
It doesnt always return accurate results.
I prefer
EndRow = Cells.Find(“*”, After:=Range(“IV65536?), searchorder:=xlByRows, searchdirection:=xlPrevious).Row
….to get the last used row.
Regards
Sam
Doug,
If you have a copy of PED, read pages 237-239. To summarise, when you hook a built-in button’s events in the way Dick shows, you’re not actually hooking *that button*, but rather that button’s *ID* – so all instances of buttons with the same ID fire the event procedure. For custom buttons, it’s the Tag that gets hooked rather than the ID (so all buttons with the same Tag fire the single event procedure).
Regards
Stephen Bullen
Although the majority of the technical side of the debate above has gone straight over my head (with a nice *WHOOSH* sound), I would just like to express my thanks, to Dick & all others contributing.
A number of tables etc in use have had issues in the past with users deleting a row “because they put something in wrong/twice/got bored/wanted to see what would happen” etc, which then throws out other dependent calculations, validation lists, or whatever, and makes life awkward… Making use of the suggestions here allows me to identify when this has happened, identify the user concerned, and go give them a good kicking. Which is always a worthwhile way to educate people in the protocols of filling things in. :)
Many Thanks!
Stephen, I do have PED. I know I’ve read those pages but I’ll read them again. I’m sure that now I’ll remember this technnique. Very cool. My thanks to you and Dick.
Can this be modified so that mRowDelButton includes IDs 292 and 293?
I just used this instead of OnAction for custom toolbar buttons in the VBE. From what I could Google, OnAction doesn’t work for VBE toolbar buttons. Or else I just didn’t guess the right string format, – I always struggle with those OnAction strings, trying to get the single-quotes, double-quotes and exclamation marks in the right place.
Anyways, this technique worked a treat.
I went back and read Excel 2002 VBA, which confirmed that OnAction doesn’t work for the VBE toolbar buttons.
I have another question about this – posted to the ng yesterday but no response:
I’m using this technique to manage buttons relating to a worksheet that’s protected with AllowFiltering. I want the user to be able to turn Filter on and off, which they can’t in a protected sheet. The only way I’ve found to do so is to set a dummy OnAction for the filter button. Specifying OnAction (or any other button property) then makes the button available, but it’s always so, e.g, even when the focus is a chart. I’ve tried the technique of copying an existing button that has the desired state performance, but once I add a tag it also becomes avaibable all the time. Is there a better way? Here’s the class module code:
Public WithEvents filter_class_button_899 As CommandBarButton
Private Sub Class_Initialize()
Set filter_class_button_899 = Application.CommandBars.FindControl(ID:=899)
filter_class_button_899.OnAction = “dummy_sub”
End Sub
Private Sub Class_Terminate()
filter_class_button_899.Reset
End Sub
Private Sub filter_class_button_899_Click(ByVal Ctrl As
Office.CommandBarButton, CancelDefault As Boolean)
Call toggle_filter ‘basically “range.Autofilter”
filter_class_button_899.State = Not filter_class_button_899.State
End Sub
Doug, yes, there is, try this:
http://www.mrexcel.com/board2/viewtopic.php?t=48869&highlight=protect+enableautofilter
Juan Pablo,
Thanks. What I am trying to do is enable the Data>Filter>AutoFilter button. What is described above seems to be the pre-2002 version of Protect AllowFiltering := True, which I’m using (in XL 03). Both of these seem to allow the user to use a filter that’s already set, but not to set and unset a Filter, i.e, click on the AutoFilter button. Of course, I’m probably just not understanding, so please help me out. :)
Interesting problem…
My solution is a little restrictive, but if you have a defined range (not necessarily named, but just a critical area)that you want to monitor, fill an unused adjacent range (Column if rows are of interest, and vice versa for columns) with numbers, and examine the sum. If the sum is different after the change event fires, you can [carefully] assume the row (or column) was deleted, and proceed with the msgbox and undo. There may be some holes in this scheme, but it works in my limited case. I hide the column that contains the numbers to avoid prying eyes…
I have looked through most of these examples; they are all good but I am not sure that I still see wht I am after.
I am working with some named ranges.
On my change event, I have some code that looks like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng1 As Range
Set Rng1 = Range(“Sh1billsW”).Resize
If Rng1.Rows.Count + 1 Then
MsgBox “Row Added”
MsgBox “Number of Rows = ” & Rng1.Rows.Count
Else
If Rng1.Rows.Count – 1 Then
MsgBox “Row Deleted”
Exit Sub
End If
End If
End Sub
It is almost there.
If I add or delete a row, it does return the proper row count in the named range.
However, anytime I do anything in the worksheet, it still runs that code.
I only want the code to run in the (event) that a row is inserted or deleted
Any ideas?
Thanks
Mark
Mark: You can’t. There’s no event for it, so you’re stuck polling a different event more times than you need.
Hello Everyone,
First of all, thanks for all your comments, I learned a lot.
One comment to Dick’s first method (you know the one where you check events after they happen), and which has the major drawback of not being able to prevent them.
You can actually make it right by simply rejecting any change that you don’t explicitly allow.
The code to do that:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim TargetWs As Worksheet
Set TargetWs = Sh
If( … ‘ I don’t like what the user is doing… ) Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub
This method also has a downside. If some code runs in the meantime that clears the undo buffer, the undo fails.
Best regards,
Balint
What about the new 2007 ribbon? This doesn’t trap the event when clicking the Home -> Delete ribbon button. Any ideas on this?
– Bob
Hey Bob,
Did you get a response to your query regarding excel 2007 on any other forum? Or did you figure it out?
Thanks
-Jairam
[…] There are no explicit methods for this. You can take a look here for some workaround methods. Daily Dose of Excel Blog Archive Capture Deleted Rows Thanks Al __________________ […]
I used this to disable row adding/deleting:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng1 As Range
Set Rng1 = Range(“ZONADATOS”).Resize
If Rng1.Rows.Count + 1 Then
MsgBox “¡cant add rows!”
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
Else
If Rng1.Rows.Count – 1 Then
MsgBox “¡cant delete rows!”
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
End If
end sub
IN THE END; ENDED UP USING:
Public glOldRows As Long
Private Sub Worksheet_Activate()
glOldRows = DataBase.Range(“ZONAAA”).Rows.count
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng1 As Range
Dim RngValue
Set Rng1 = Range(“ZONAAA”)
If Rng1.Rows.count glOldRows Then
MsgBox “¡No se pueden modificar las filas!”
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
Dick – thank you for your code. First of all, let me say that I couldn’t get the first example (Worksheet_Change) to work properly. It kicked in when the user right-clicked on the row, but at that time, the row HADN’T been deleted. Once the Worksheet_Change had run, I could THEN delete the row, but the Worksheet_Change didn’t kick in for the actual deletion of the row.
I then tried the class exampe, and that worked perfectly. I should add that I’m running Excel 2007 with XP and have customized the ribbon so that the Edit options are disabled.
Igor’s probably moved on since then, but I couldn’t get his example to work either. I kept getting a 424 on the line gl_OldRows = database.Range(“ZONAAA”).Rows.Count
Thanks again Dick