WorkSheet_SelectionChange Not Working (Tried the obvious stuff)

[Johndinho]

Hi

I have the following code in the sheet where the target cells (H20 & N20) are

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$H$20" Then
    DespatchAddress
End If

If Target.Address = "$N$20" Then
    ReceiptAddress
End If

End Sub

The expectation is that when the values in H20 or N20 are changed the relevant code is triggered but instead nothing happens

For reference the associated code is in a separate moduel and looks like looks this for both change events

Sub DespatchAddress()

Application.ScreenUpdating = False

'Insert lookup formulae to populate address details
    Range("F12:I18,F21:I27,F29:I29,F31:I31,F33:I33,D36:I36").Select
    Selection.FormulaR1C1 = _
        "=IF(HLOOKUP(R20C8,Addresses!R1:R33,ShippingTicket!RC3,FALSE)="""","""",HLOOKUP(R20C8,Addresses!R1:R33,ShippingTicket!RC3,FALSE))"

'Replace formulae with values
    Range("F12:I18").Select
    With Selection
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Range("F21:I27").Select
    With Selection
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Range("F29:I29").Select
    With Selection
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Range("F31:I31").Select
    With Selection
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Range("F33:I33").Select
    With Selection
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Range("D36:I36").Select
    With Selection
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Range("D39").Select
    Application.CutCopyMode = False

Application.ScreenUpdating = True

End Sub

If I run this code via F5; all works fine

I have tried
EnableEvents is true using ?Application.EnableEvents in the immediate window
Application.EnableEvents = True in the immediate window
Application.EnableEvents = True in a separate module and ran it
Application.EnableEvents = True at the start of the worksheet change code
In a separate sheet entered the code

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "running"

End Sub

(This suggestion was taken from another post on here and works)

But my SelectionChange code is not working when I change H20 or N20

Any ideas would be appreciated

Thanks

 

[James006]

Hi,

You could test

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$20" Then DespatchAddress
If Target.Address = "$N$20" Then ReceiptAddress
End Sub

Hope this will help

 

[Johndinho]

Awesome

I have no idea why this works and my code doesn't (Mine works in another workbook)

Thanks 006!! :D

 

[James006]

You are welcome ...