How to Run a Macro if the cell value changes

Question

I created a macro that filters based on a cell value which works fine.

Range("A1:L1").AutoFilter Field:=4, Criteria1:=Range("U1")

I need this macro to run everytime the cell value changes. I wrote a macro but it is not working i dont get any errors just nothing happens.

I tried:

Private Sub Worksheet_Tabelle1(ByVal Target As Range)

If Target.Address = "$U$1" Then

Application.EnableEvents = False

Range("A1:L1").AutoFilter Field:=4, Criteria1:=Range("U1")

Application.EnableEvents = True

End If

End Sub

This version should just execute the code and not call a macro. I changed the Worksheet_xxxxx to the sheet name and tried other things.

I also tried:

Private Sub Worksheet_Arbeitstabelle(ByVal Target As Range)

If Target.Address = "$U$1" Then

Call Macro1

End If
End Sub

This version should call the following Macro:

Sub Macro1()


Range("A1:L1").AutoFilter Field:=4, Criteria1:=Range("U1")

End Sub

I put all the Private Sub macros on the Worksheet and the Macro1 in a modul. The file is .xlsm and doesnt have any problem running other macros so i dont know why its not working. My guess is i probably did something wrong with the names so here are the names:

Possible duplicate of [VBA Worksheet Change Event](https://stackoverflow.com/questions/10474356/vba-worksheet-change-event) — FunThomas, Apr 08 '19 at 08:30
https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640 — Siddharth Rout, Apr 08 '19 at 08:36
@FunThomas I mentioned in the Question where i put the Code its not in a modul — Cedric, Apr 08 '19 at 08:48
@SiddharthRout Thanks i read the answer and changed the Worksheet_Tabelle to Worksheet_change now its working thanks alot — Cedric, Apr 08 '19 at 08:49

score 1 · Answer 1 · answered Apr 08 '19 at 08:32

1

Try this:

    Dim KeyCells As Range
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:C10")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Display a message when one of the designated cells has been 
        ' changed.
        ' Place your code here.
        MsgBox "Cell " & Target.Address & " has changed."

    End If
End Sub

answered Apr 08 '19 at 08:32

Adam Thompson

21
5

Thanks for the suggestion but i found another solution – Cedric Apr 08 '19 at 08:49
@Cedric the Intersect function is actually preferable. You'd be able to run the macro if the changed range corresponds with one or more ranges. Consider changing your `If Target.Address...` line. – Tim Stack Apr 08 '19 at 09:07

score 0 · Answer 2 · answered Apr 08 '19 at 08:55

I changed:

Private Sub Worksheet_Arbeitstabelle(ByVal Target As Range)

To:

Private Sub Worksheet_Change(ByVal Target As Range)

@Siddharth Rout linked me to: Why MS Excel crashes and closes during Worksheet_Change Sub procedure?

Here it is explained that its not necessary to add the name of the Sheet after Worksheet_ because the code is stored in the sheet so no need to tell it where to do stuff since its not in a module

How to Run a Macro if the cell value changes

2 Answers2