Worksheet_Change Event problem

[r1998]

Dear friends and respected seniors,
in excel in sheet1, whenever the value in cell F2 changes, it will store the new value in F14 if its blank, then another value in F15 if its blank and then another value in F16 if its blank...
I am using this vba code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("f2")) Is Nothing Then
   If IsEmpty(Range("f14")) Then
   Range("f14") = Target.Value
   ElseIf IsEmpty(Range("f15")) Then
   Range("f15") = Target.Value
   ElseIf IsEmpty(Range("f16")) Then
   Range("f16") = Target.Value
   End If
  End If
End Sub

This works perfectly fine if i am manually changing the value with keyboard in cell F2.....
But when i use the RTD function in cell F2, the value in F2 cell changes after every 2-3 seconds but values in f14,f15 and f16 dont show up
i have no clue what i am doing wrong
Can anyone please kindly guide me n point out my mistake
Awaiting your replies.
Thank you.

 

[James006]

Hello,

what i am doing wrong

... Nothing ... .wink:

The Worksheet_Change will not be fired by a Live feed ...

Do not know the structure of your worksheet ... but you could be testing the Woksheet_Calculate ....

Hopefully this will help

 

[offthelip]

Firstly when you change a cell with in a worksheetchange event subroutine, you are changing the worksheet which will trigger a worksheet changeevent. i.e you reenter the same sub routine before you have finished it the first time, this is a recipe for probelms usually an "out of memory" error. You get away with it this time because you are checking if the change is cell f2, which fails on the 2nd entry. To avoid this you should disable events before you write to a cell in the sub, but you mnust restore them afterwards:
like this:

  Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("f2")) Is Nothing Then
[COLOR=#ff0000]   Application.EnableEvents = False[/COLOR]


   If IsEmpty(Range("f14")) Then
   Range("f14") = Target.Value
   ElseIf IsEmpty(Range("f15")) Then
   Range("f15") = Target.Value
   ElseIf IsEmpty(Range("f16")) Then
   Range("f16") = Target.Value
   End If
[COLOR=#ff0000]   Application.EnableEvents = True[/COLOR]


  End If
End Sub

that wasn't the question you asked.
What might be causing your probelm is the RTD update is not triggering the worksheet change event, one way you might be able to get around this is to put a calculation into a spare cell which uses the cell F2 . e.g =len(F2)
This cell will cause a worksheet calculate event which you can use to trigger your code. Note the worksheet calculate envent does not have a "Target Range" so you will need to test to see if F2 is different from the previous value,

 

[r1998]

Thank you James006 and thank you offthelip
i will test with worksheet_calculate and by disabling events too
i will be able to test tomorrow n will reply back
Thank you

 

[James006]

You are welcome ...

 

[r1998]

Thank you James006 and thank you offthelip
final working code

Option Explicit
  
  Private Sub Worksheet_Calculate()
    Dim target As Range
    Set target = Range("f2")

    If Not Intersect(target, Range("f2")) Is Nothing Then
    If IsEmpty(Range("f14")) Then
   Range("f14") = target.Value
   ElseIf IsEmpty(Range("f15")) Then
   Range("f15") = target.Value
   ElseIf IsEmpty(Range("f16")) Then
   Range("f16") = target.Value
   End If
   End If
End Sub

 

[James006]

Glad to hear you could fix your problem ...

 

[offthelip]

It is best practice to inhibit the events when you are writing into the worksheet Calculate event as well. because you can trigger a recalculate with every write to the worksheet depending what is on it. You might need to do a specific recalculate at the end of all the writing, but the point I am making is you should keep control of it. Otherwise you might add a fomula to you worksheet and then find your vba code crashes
Also your check on the intersect will always pass because you set target to F2 , so this is totally unnecessary
so I recommend:

  Private Sub Worksheet_Calculate()

   Application.EnableEvents = False
    If IsEmpty(Range("f14")) Then
   Range("f14") = target.Value
   ElseIf IsEmpty(Range("f15")) Then
   Range("f15") = target.Value
   ElseIf IsEmpty(Range("f16")) Then
   Range("f16") = target.Value
   End If
   Application.EnableEvents = True
   
End Sub