Worksheet_Change(ByVal Target As Range) NOT WORKING

[velocity1234]

I have the following code in worksheet 1.

I have a RTD feed linked to $F$13 and $F$14. The code will record Higher Highs and Lower Lows from the RTD Feed in Cells F13 and F14. However, when the feed is on and linked to the data source the code does not run. If I type over the code in cell F13 or F14 with a hard number the code works fine.

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$13" Then
If Target.Value - Target.Offset(0, 2) > 0 Then
Target.Offset(0, 2) = Target.Value
ElseIf Target.Value - Target.Offset(0, 3) < 0 Then
Target.Offset(0, 3) = Target.Value
End If
End If

If Target.Address = "$F$14" Then
If Target.Value - Target.Offset(0, 2) > 0 Then
Target.Offset(0, 2) = Target.Value
ElseIf Target.Value - Target.Offset(0, 3) < 0 Then
Target.Offset(0, 3) = Target.Value
End If
End If

End Sub

I have also tried adding Application.EnableEvents = True to the code, but no luck in getting it to fire off. What am I doing wrong?

Thanks,

 

[mumps]

As far as I know, the Worksheet_Change macro isn't executed if a cell is changed by a formula or data import but it does work when data is entered manually. Maybe someone else in the forum has some insight into this problem.

 

[dmt32]

Nothing wrong with your code and as already pointed out, this event is not triggered by calculation.

You need to add a Calculation event to call the procedure but unfortunatley, this event does not have a range argument so need to test for cell change when calculation occurs - but to do this you need a helper cell.


add this code to your sheets code page & see if does what you want. You will need to change the sheet index shown in red or add name if different & adjust the helper cells (Z13 & Z14) if needed.

Private Sub Worksheet_Calculate()
    Dim Addrss As String
    On Error GoTo myerror
    Application.EnableEvents = False
    If Me.Range("Z14").Value <> Me.Range("F14").Value Then
        Me.Range("Z14").Value = Me.Range("F14").Value
        Addrss = "$F$14"
    ElseIf Me.Range("Z13").Value <> Me.Range("F13").Value Then
        Me.Range("Z13").Value = Me.Range("F13").Value
        Addrss = "$F$13"
    End If
    If Len(Addrss) > 0 Then Worksheet_Change (ThisWorkbook.Sheets(1).Range(Addrss))
myerror:
    Application.EnableEvents = True
End Sub

Hope helpful
Dave

 

[velocity1234]

Hi Dave,

I get a compile error when I cut and paste the code. Any thoughts on how to fix?

I am in Sheet1

thanks,

 

[dmt32]

whats the error?

 

[velocity1234]

Compile error:

Sub or Function not Defined


The following is highlighted in red:

If Len(Addrss) > 0 Then Worksheet_Change (ThisWorkbook.Sheets(1). Range (Addrss))

 

[dmt32]

You need to paste the code in the same sheet code page as your worksheet_change event code.

Dave

 

[velocity1234]

Hmmmm.....

This is what the code looks like when I double click on Sheet1 in VBA Microsoft Excel Objects folder.

Private Sub Worksheet_Calculate()
Dim Addrss As String
On Error GoTo myerror
Application.EnableEvents = False
If Me.Range("Z14").Value <> Me.Range("F14").Value Then
Me.Range("Z14").Value = Me.Range("F14").Value
Addrss = "$F$14"
ElseIf Me.Range("Z13").Value <> Me.Range("F13").Value Then
Me.Range("Z13").Value = Me.Range("F13").Value
Addrss = "$F$13"
End If
If Len(Addrss) > 0 Then Worksheet_Change (ThisWorkbook.Sheets(1).Range(Addrss))
myerror:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$13" Then
If Target.Value - Target.Offset(0, 2) > 0 Then
Target.Offset(0, 2) = Target.Value
ElseIf Target.Value - Target.Offset(0, 3) < 0 Then
Target.Offset(0, 3) = Target.Value
End If
End If

If Target.Address = "$F$14" Then
If Target.Value - Target.Offset(0, 2) > 0 Then
Target.Offset(0, 2) = Target.Value
ElseIf Target.Value - Target.Offset(0, 3) < 0 Then
Target.Offset(0, 3) = Target.Value
End If
End If

End Sub

 

[dmt32]

code works fine for me but if still having problems try removing the word "Private" from Change Event procedure.

Dave

 

[velocity1234]

I have tried a lot of different ways on this. Still no luck.

Does anyone have any other ideas?

thanks