CODEWITHSUNDEEP
excelvba
Cedric
Cedric

Reputation: 31

How to Run a Macro if the cell value changes

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:

All Sheets with names

Upvotes: 0

Views: 911

Answers (2)

Cedric
Cedric

Reputation: 31

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

Upvotes: 0

Adam Thompson
Adam Thompson

Reputation: 21

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

Upvotes: 1

Related Questions