Strange behavior of Worksheet_change()

[Håkan Björkström]

Hi

I had confusing problem in a larger project and tried to test it in an empty worksheet. When I enter a string in C6 it triggers Worksheet_Change. Worksheet_Change should write "Yes" into E6.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 And Target.Row > 5 Then
        Cells(Target.Row, Target.Column + 2) = "Yes"
    End If
End Sub

What happens? Yes it writes "Yes" to the cell, but it is not visible until I re-edit it (F2) (or another cell) and press Enter.

If I put a breakpoint to any line in Workssheet_Change, program wouldn't stop at that point when I enter the string - only when I delete it.

I use Office 2007

Any idea???

/Håkan

[Michael Munch]

Hi Håkan

 

 

I can not see any errors in your code, so I can not answer why it does not work. Your code works fine with my Excel.

I'm working with Excel 2016 and your code will look like this with a little optimization:

 

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Column = 3 And Target.Row > 5 Then

        Application.EnableEvents = False  'So it don't run twice

        Cells(Target.Row, Target.Column + 2) = "Yes"

        Application.EnableEvents = True

    End If

End Sub

 

 

Best regards

Michael

--
You received this message because you are subscribed to the Google Groups "excel vba" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-vba+...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-vba.
For more options, visit https://groups.google.com/d/optout.

[Håkan Björkström]

Takk Michael!