Worksheet_Change event on automatically changing cell

drag1c

Board Regular
Joined
Aug 7, 2019
Messages
92
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Hi,

Im using automatical change of value in cell J2 (formula dependent), how should I setup next code to recognize the change of cell value J2 ?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Sheet2.Range("J2")) Is Nothing Then
    Sheet8.ListObjects("Table2").Range.AutoFilter Field:=15, Criteria1:=Sheet2.Range("J2").Value
End If
End Sub

Right now, this code works, but only if I manually change value in J2.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Have never done so, but I'd say use Calculate event - as long as you have not turned off automatic calculation I guess.
Change event isn't fired by formula causing the change.
 
Upvote 0
Try this, using a 'helper cell' somewhere out of the way on the sheet, Z2 used here
VBA Code:
Private Sub Worksheet_Calculate()
    If Range("J2").Value <> Range("Z2").Value Then
        Application.EnableEvents = False
        MsgBox "just to show triggered by formula result of j2"
        Range("Z2").Value = Range("J2").Value
        Application.EnableEvents = True
        'Sheet8.ListObjects("Table2").Range.AutoFilter Field:=15, Criteria1:=Sheet2.Range("J2").Value
    End If
End Sub
 
Upvote 0
Hi,
You can use worksheet_Calculate event to detect the formula change but this does not have the Target parameter to check which cell made the change.
A workaround to prevent unnecessary repetition would be to use the Range.ID property of the cell as a tag & check against this for any change in cell value & if so, only then run your filter code

untested but try following & see if helps you
VBA Code:
Private Sub Worksheet_Calculate()
    With Me.Range("J2")
        If Val(.ID) <> .Value Then
        .ID = .Value
        Sheet8.ListObjects("Table2").Range.AutoFilter Field:=15, Criteria1:=.Value
        End If
    End With
End Sub

Note: Range.ID does not retain its value after workbook is closed.

Dave
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,585
Messages
6,114,520
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top