Pasting won't trigger Worksheet_Change...HELP!

[bspace2]

Hi All,

I've searched on other posts and cannot come up the answer. I have the following code where I need the time (Now) to display when there is a value in Column O. However if something is pasted into O it does not trigger the Worksheet_Change. Please help!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim S As Range, O As Range, t As Range

Set S = Range("S:S")
Set O = Range("O:O")
Set t = Target

If Intersect(t, O) Is Nothing Then Exit Sub

Application.EnableEvents = False
Range("S" & t.Row).Value = Now
Application.EnableEvents = True
End Sub

 

[wideboydixon]

What version of Excel is this? On what operating system?

WBD

 

[bspace2]

Hi,

Its Windows 7....Excel 2010. Version 14.0.7165.5000 (32-bit)

Thanks

 

[Fishboy]

Hi bspace2, welcome to the boards.

As your code is working fine at my end my first reaction would be that if the code had errored in the past it means Application.EnableEvents is probably still set to False. Try running this simple code real quick then trying your Worksheet_Change again:

Sub EnableEvents()
Application.EnableEvents = True
End Sub

 

[bspace2]

Hi Fishboy, appreciate the welcome and responses.

Maybe I should be slightly more clear. If I paste a singular cell like O2 to O3 the code works. However, if I'm pasting into multiple Cells, say I want to paste the word "Resolved" in O2 to O5:O9 and do so by copying then highlighting the cells and pasting, it will not update all cells. Any ideas or does that work on your end?

Thanks,

 

[Fishboy]

Hi Fishboy, appreciate the welcome and responses.

Maybe I should be slightly more clear. If I paste a singular cell like O2 to O3 the code works. However, if I'm pasting into multiple Cells, say I want to paste the word "Resolved" in O2 to O5:O9 and do so by copying then highlighting the cells and pasting, it will not update all cells. Any ideas or does that work on your end?

Thanks,

Hmm, try the following instead of your existing code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range


If Not Intersect(Target, Range("O:O")) Is Nothing Then
    If Target.Cells.Count >= 1 Then
        For Each Cell In Selection
            Application.EnableEvents = False
            Range("S" & Cell.Row).Value = Now
            Application.EnableEvents = True
        Next Cell
    End If
End If


End Sub

 

[wideboydixon]

Hi Fishboy, appreciate the welcome and responses.

Maybe I should be slightly more clear. If I paste a singular cell like O2 to O3 the code works. However, if I'm pasting into multiple Cells, say I want to paste the word "Resolved" in O2 to O5:O9 and do so by copying then highlighting the cells and pasting, it will not update all cells. Any ideas or does that work on your end?

Thanks,

Yep. That kind of information would be invaluable to start with. You suggested the Worksheet_Change event wasn't triggering at all; that's not the case.

WBD

 

[bspace2]

Thank you FishBoy. Much appreciated!

 

[Fishboy]

Thank you FishBoy. Much appreciated!

Happy to help.

 

[bspace2]

Hi Fishboy,

One more question. The logic on this is working most of the time. However, if I type "RESOLVED" in a cell and hit Enter it's actually changing the cell one below that of which I typed into in Column S. If I type "RESOLVED" and arrow over, it's updating the correct corresponding cell in Column S. IF you know how to fix this, great. If not I appreciate your time in all this.

Thanks