If Cell (odds) changed, do it

[rain91]

I would like to, if K9 changed (LTP odds), somethink happen.

My code works if i manually mod K9 cell value, but if the change is coming from Betangel, it isnt work.
Why?

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Address = "$K$9" Then

     MsgBox "This Code Runs When Cell K9 Changes!"

End If

Application.EnableEvents = True

End Sub

[ODPaul82]

When BA refreshes the spreadsheet its refreshing it in ranges rather than individual cells (well it does do some individual cells)

You're attempting to check an individual cell whereas Target.Address is going to be something like $K$9:$K$100
If you wanted to check individual cells you could use Instr or Intersect

Also toggling EnableEvents Off/On consistently like that I wouldn't recommend, I only switch it off when I need to actually do something with a cell value on the sheet.

[rain91]

When BA refreshes the spreadsheet its refreshing it in ranges rather than individual cells (well it does do some individual cells)

You're attempting to check an individual cell whereas Target.Address is going to be something like $K$9:$K$100
If you wanted to check individual cells you could use Instr or Intersect

Also toggling EnableEvents Off/On consistently like that I wouldn't recommend, I only switch it off when I need to actually do something with a cell value on the sheet.

Thanks for the reply, but it works not with this method
In this case i get the "Hello" msg in every seconds (refresh rate in Betangel is 1sec), but the value of K9 isnt changed.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("K9")) Is Nothing Then

MsgBox "Hello"

End If

End Sub

[ODPaul82]

Because it doesn't have to change, Worksheet_Change will trigger every second because the countdown timer triggers every second on the worksheet and so therefore kicks off the routine.

If you wanted to just check K9 to see if it has changed then use this, I've not tested it but should work

Code: Select all

Private mdblLTP_SingleCell As Double

Private Sub Worksheet_Change()
    If Me.Range("K9").Value <> mdblLTP_SingleCell Then
      Application.EnableEvents = False
      DoSomethingElseHere
      mdblLTP_SingleCell = Me.Range("K9").Value
      Application.EnableEvents = True
   End If
   
End Sub