Detect on worksheet change if user is deleting

Question

I'd like to know how to detect if the user is deleting or inserting content into a range. If they are deleting a range say D14:D18. I'd like to then perform a macro that also deletes content in E14:E18. I just wouldn't want to delete E14:E18 if they are entering content into D14:D18.

I've tried:

If Selection.ClearContents Then
    MsgBox Target.Offset(0, 3).Style
End If

But this get's me stuck in an infinite loop.

A bit more context:

I have a few hundred cells in D:D for entering quantities for services. Not everything in D:D should be touched. Only cells in D:D with .Style = "UnitInput". In E:E I have data validation that lets the user only enter contractor 1 or contractor 2 But, when content is entered in D:D I run a macro to assign the default contractor (housed in F:F) to E:E. So when the user enters quantities into D:D it correctly assigns the default contractor. And when they delete singular items from D:D I have it handling proper removal of contractors. It's only when they delete a range of items from D:D.

Full code:

 Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    On Error GoTo ErrHandler:
    If Selection.Rows.Count * Selection.Columns.Count = 1 Then
        If Target.Offset(0, 3).Style = "Contractor" Then
            If Target.Value < 1 Then
                Target.Offset(0, 3).Value = ""
            Else
                Target.Offset(0, 3).Value = Target.Offset(0, 2).Value
            End If
        End If

        If Target.Offset(0, 5).Style = "Markup" Then
            If Target.Value = "" Then
                Target.Offset(0, 5).Value = ""
            ElseIf Target.Value <= Target.Offset(0, 14).Value Then
                Target.Offset(0, 5).Value = "Redact 1"
            ElseIf Target.Value >= Target.Offset(0, 15).Value Then
                Target.Offset(0, 5).Value = "Redact 2"
            Else
                Target.Offset(0, 5).Value = "Redact 3"
            End If
        End If
    Else
        '!!!!!! this is where I need to handle multiple deletions. !!!!!!!
    End If

    Application.ScreenUpdating = True
ErrHandler:
    Application.ScreenUpdating = True
    Resume Next
End Sub

Store the values of that range in an array on `Workbook_open()`, then in the worksheet change event, check if the values were cleared — Siddharth Rout, Mar 24 '16 at 12:06
@TheGuyThatDoesn'tKnowMuch, yeah, i guess so, was using them interchangeably — click here, Mar 24 '16 at 12:06
You may also see [This](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) on how to use `Interesect` in worksheet change event to check for the changes in that range. — Siddharth Rout, Mar 24 '16 at 12:09
Also a quick question. Why not use a formula in `E` range? `=If(D14="",E14="","Whatever you want to do")` — Siddharth Rout, Mar 24 '16 at 12:13
@SiddharthRout, can't use a formula, there's already both data validation for that range and macro's associated with populating that Range based off what's done in the D:D column. And it's a lot more than just 5 rows, it's any combination of many in D:D. I've figured out all aspects except dealing with the user deleting more than one item in D:D. The boss wants them to be able to do that, so I need that functionality handled properly. — click here, Mar 24 '16 at 12:15
Perhaps I didn't understand your `workbook_open()` suggestion but as I understand it, it would seem to complicate my current problem significantly. I'm going to add more context to my post now — click here, Mar 24 '16 at 12:21
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/107218/discussion-between-siddharth-rout-and-click-here). — Siddharth Rout, Mar 24 '16 at 12:21

score 4 · Accepted Answer · answered Mar 24 '16 at 12:46

Based on your comments in chat, here is what I propose

UNTESTED

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, aCell As Range
    Dim lRow As Long

    '~~> Error handling, Switching off events and Intersect
    '~~> As described in
    '~~> http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs
    On Error GoTo Whoa

    Application.EnableEvents = False

    With ActiveSheet
        '~~> Find Last Row since data is dynamic
        '~~> For further reading see
        ' http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        End If

        If lRow > 12 Then
            '~~> Set your range
            Set rng = Range("D13:D" & lRow)

            If Not Intersect(Target, rng) Is Nothing Then
                For Each aCell In rng
                    If Len(Trim(aCell.Value)) = 0 Then
                        Select Case Target.Offset(0, 3).Style
                        Case "Contractor"
                            '~~> Do Your Stuff
                        Case "Markup"
                            '~~> Do Your Stuff
                            '
                            '~~> And so on
                            '
                        End Select
                    End If
                Next aCell
            End If
        End If
    End With

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

score 0 · Answer 2 · answered Mar 24 '16 at 12:15

Here is an idea -- you have to first select a region to clear its contents. Use selection change to record the number of non-blank cells and then worksheet change to see if it drops to zero. Something like:

Dim NumVals As Long

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NewCount As Long
    NewCount = Application.WorksheetFunction.CountA(Target)
    If NewCount = 0 And NumVals > 0 Then MsgBox Target.Address & " was cleared"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    NumVals = Application.WorksheetFunction.CountA(Target)
End Sub

I have this code is Sheet1 and it seems to capture when I highlight a group of cells (which contains at least one value) and then hit the delete key.

Scott Holtzman · Answer 3 · 2016-03-24T12:24:37.533

0

You can use the CommandBars Undo Control to determine if the user has actually deleted something.

Bear in mind this will fire if the user any or all of the contents of the Range D14:D18, but can be adjusted in many ways to suit your exact needs. After seeing your edit, this basically means you can adjust the ranges and need be and which cells in column E it affects as well. If you need more guidance on this, let me know.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("D14:D18")) Is Nothing Then

    Dim sLastAction As String
    sLastAction = Application.CommandBars("Standard").Controls("&Undo").List(1)
    Debug.Print sLastAction

                    'manual delete            'right-click delete                 'backspace delete
    If sLastAction = "Clear" Or sLastAction = "Delete" Or Left(sLastAction, 9) = "Typing ''" Then

        Application.EnableEvents = False
        Me.Range("E14:E18").ClearContents
        Application.EnableEvents = True

    End If

End If

End Sub

edited Mar 24 '16 at 12:24

answered Mar 24 '16 at 12:17

Scott Holtzman

27,099
5
37
72

2

No this is a wrong way to do it. Try clearing data by pressing Backspace ;) – Siddharth Rout Mar 24 '16 at 12:21
4

And what if user pastes a blank cell on that range and hence clearing off the data? Don't want to sound cheeky here but like I said this is the complex way of doing it :) The best IMHO would be what I suggested in comments. – Siddharth Rout Mar 24 '16 at 12:28
@SiddharthRout - you do a raise a good point there as well, in the sense of capturing everything. However, the code can be easily adjusted to account for that scenario as well. Unless there's a lot of other ways to get a "blank" cell, I think it's almost covered with what I have and a possible edition of pasting blank cells. Admittedly, your approach may be cleaner / easier. The advantage of this approach is that the code is all contained in one module in one space so it's a bit easier to follow / maintain going forward. The good thing is that there are many was to skin a cat in Excel VBA :) – Scott Holtzman Mar 24 '16 at 12:31

score 0 · Answer 4 · answered Mar 24 '16 at 15:13

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ChangedRange As Range
Dim Area As Range
Dim Cell As Range

Set ChangedRange = Application.Intersect(Target, Range("D:D"))

If Not ChangedRange Is Nothing Then
    Application.EnableEvents = False

    For Each Area In ChangedRange.Areas
        For Each Cell In Area
            If IsEmpty(Cell) Then
                Cell.Offset(0, 1).ClearContents
            End If
        Next
    Next

    Application.EnableEvents = True
End If

End Sub

Detect on worksheet change if user is deleting

4 Answers4

Linked