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
– user4039065, Commented Oct 23, 2017 at 16:06
Does the other module making the change turn off event handling when it makes the change? — Tim Williams
– Tim Williams, Commented Oct 23, 2017 at 16:09
@jeeped i tried with Application.enableevents = false previously but still same — Avi Sangray
– Avi Sangray, Commented Oct 23, 2017 at 16:11
Regardless of fixing the problem or not you should be implementing it. — user4039065
– user4039065, Commented Oct 23, 2017 at 16:12
Worksheet_Change is not triggered by calculations - you need the Calculate event. — Tim Williams
– Tim Williams, Commented Oct 23, 2017 at 16:40

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

0

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

answered Oct 23, 2017 at 17:14

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mathieu Guindon Over a year ago

No events will fire if EnableEvents is toggled off. Other than that, this code works for anyone that tries it.

Mathieu Guindon Over a year ago

@AviSangray protip: if you want help, "na not working" is the single most useless way to describe what the problem is. Best of luck, I'm done here.

Avi Sangray Over a year ago

@mats-mug what else i should have said? :-|. i myself dont know what's the issue

Avi Sangray Over a year ago

there wasn't anything else to say

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related