excel on change not working if cell value is changed by another module

Question

i have a range whose value is changed realtime but the onchange module does nothing if value is changed by other module for that range. however if i change value manually it works. code :-

      Private Sub Worksheet_Change(ByVal Target As Range)

 Dim rng As Range
 Dim lastRow As Long
Dim cell As Range


 If Not Intersect(Target, Range("J10:J43")) Is Nothing Then

  Application.EnableEvents = False

    For Each cell In Target

    If cell.Value < cell.Offset(0, 4).Value Then
    cell.Offset(0, 7).Value = cell.Offset(0, 1).Value
    'Module1.OnGenOrder

         End If
        Next cell




   End If

  Application.EnableEvents = True

 End Sub

NOTE:- i think module Private Sub Worksheet_Change(ByVal Target As Range) is not able to sense changes. The value is changed by a module in another external .xla file. but a change by simple formulas like =a1+b1 works well

update

this is code of cell to monitor

 =c:\Excelmacros\updateprice.xla!dataupdate($H12,"price1")

For starters, if you are going to change the values on that worksheet you should use Application.enableevents = false to avoid it running on top of itself. Perhaps it is and you are triggering an error control routine. — user4039065
Does the other module making the change turn off event handling when it makes the change? — Tim Williams
@jeeped i tried with Application.enableevents = false previously but still same — Avi Sangray
Regardless of fixing the problem or not you should be implementing it. — user4039065
Worksheet_Change is not triggered by calculations - you need the Calculate event. — Tim Williams

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2017-10-23T17:14:51

Event handler procedures have a simple naming convention:

Private Sub [EventSource]_[EventName]([args])

Seeing how the event source is Worksheet, it looks like your handler is in some worksheet's code-behind module; that will only ever respond to Change events on that worksheet.

If you want to handle Change events on any worksheet in ThisWorkbook, then handle the SheetChange event of the Workbook class, in the code-behind module for ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

Notice how the changed sheet is being received as a parameter.

If you want to handle Change worksheet events on any worksheet in another workbook, then you need a class module and a WithEvents field - the ThisWorkbook code-behind can serve (a workbook is a class, after all), for simplicity's sake:

Private WithEvents app As Excel.Application

You'll need to Set that app event source to a valid Excel.Application object reference as appropriate (say, in the Open handler for ThisWorkbook), and then you can handle application-wide events:

Private Sub Workbook_Open()
    Set app = Excel.Application
End Sub

Private Sub app_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   MsgBox "Cell " & Target.Address(External:=True) & " was changed."
End Sub

excel on change not working if cell value is changed by another module

1 Answers