CODEWITHSUNDEEP
excelvba
Ethan Brown
Ethan Brown

Reputation: 5

How to run a macro when data is added to a cell

I want to run a macro when data is added to a cell.

I found advice on how to run a macro when data is changed in a cell. This won't work. If data is removed from a cell I don't want the macro to run.

I tried an intersect function. I can only make my code run macros when the cells change as opposed to when data is added.

This is my current code:

Sub Worksheet_Change(ByVal Target As Range)
    'detect data in cell
    If Not Intersect(Target, Range("J13:J27")) Is Nothing Then
        Call Copy_Cell
    End If
End Sub

Upvotes: 0

Views: 110

Answers (1)

FunThomas
FunThomas

Reputation: 29146

There is no other event that you can use, the Change-event is the right place to go. All you need to do is to check if the modified cell(s) contain something or not.

Now when the change-event is triggered, more than one cell can be modified (eg by Cu&Paste), so you will likely need to check all modified cells individually.

As you don't show the code of Copy_Cell, I can only assume that this routine is copying something - likely using ActiveCell. You should change that routine and let it receive the cell to be copied as parameter.

Also, you need to be aware that if Copy_Cell is writing something in the same worksheet, the Change-Trigger is called recursively. To avoid that, use Application.EnableEvents = False.

Your code could look like this:

Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Change_exit ' Ensure that Events are enabled even if an error occurs
    Application.EnableEvents = False
    
    Dim cell As Range
    For Each cell In Target
        If Not Intersect(cell, Range("J13:J27")) Is Nothing And Not IsEmpty(cell) Then
            copy_Cell cell
        End If
    Next cell

Change_exit:
    Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions