problem with execute macro when change value of specific cell

[maabadi]

Hi
I record this macro for auto refresh filter of table based of value of cell "B2"

Sub Mysub()
'
' RefreshFilters Macro
'

'
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
        "<>"
End Sub

And Before that I define this Worksheet_Change macro command

Private Sub Worksheet_Change(ByVal Target As Range)
  Call RefreshFilters(Me.Name, Target.Address, "$B$2")
End Sub

But it didn't work and didn't execute macro when cell "B2" value is changed by hand.

what is problem
thanks

 

[Andy2016]

Events in VBA can be sensitive. Did you write

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)

by yourself or use the dropdown menu?

If you didnt use the dropdown menu you may find it doesnt work[/COLOR]

 

[dmt32]

Hi,
As an observation, your Worksheet Change event is calling the procedure RefreshFilters which has three parameters.

The Code you show here is named MySub with no parameters? So you change event will not be call this macro. If the code is not erroring when cell change is made then clearly the RefreshFilters code exists & would be helpful to board if you share that code.

Can I hazard a guess that following may be something like what you are trying to do:

In a Standard module:

Sub RefreshFilters(ByVal Target As Range)
'
' RefreshFilters Macro
'
'
    With Target.Parent.ListObjects("Table1")
        .Range.AutoFilter Field:=Target.Column
        .Range.AutoFilter Field:=Target.Column, Criteria1:="<>"
    End With
End Sub

In your Worksheest Code Page:

 Private Sub Worksheet_Change(ByVal Target As Range)    
   If Target.Address = "$B$2" Then Call RefreshFilters(Target)
End Sub

Dave

 

[maabadi]

thanks dmt32
you're right.
It worked after I right click on sheet name and view code and after that import codes.