SheetChange event doesn't fire when using paste

[clu82]

I am capturing events using WithEvents. When I modify a cell by typing in it, the App_SheetChange() function fires correctly. When I paste data in the cell then click a different cell, the event doesn't fire. Is this normal operation? Has anyone ran across this?

tia,

flynn

 

[jimboy]

can you post the code, it might exit if more than one cell is selected?

 

[clu82]

This is the code. I just tried it again. This time I pasted a value in the cell then exited the cell. The event did not fire. I then double clicked in the cell, went to the end of the data that was pasted, add space character, pressed enter and the event then fired. It looks like pasting doesn't fire the event.

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Dim sDescr As String
    
If Target.Worksheet.Name = "Check list" Then
    If Target.Column = Range("HdrAssyPN").Column Then
        If Target.Value <> "" Then
            Application.EnableEvents = False
            Call GetDescription(Target.Value, sDescr)
            Cells(Target.Row, Range("HdrAssyDesc").Column).Value = sDescr
            Application.EnableEvents = True
        End If
    End If
End If
End Sub

 

[Nimrod]

A Worksheet's Worksheet_Change event works for paste ..... how about using that instead ?

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "test"
End Sub

 

[clu82]

Nimrod,

I just tried the using the Worksheet_Change() event, but when I paste data into a cell, it doesn't fire. When I type something in, it fires. Not sure what I'm doing wrong if it works for you.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    MsgBox "Worksheet_Change() event"
End Sub

 

[Ivan F Moala]

Try turning your Events bacvk on ?? or checking that there
are no conflicting Event handlers eg you may have anotherevent handler that is turning it off........

Run This

Msgbox Application.Enableevents
Application.Enableevents = true

 

[Chunder]

A bit of a bump

I've just stumbled across this problem (Excel 97 SR-2) and can't for the life of me figure out why it doesn't work!

Using numerous tips from this site, my spreadsheet now stores the contents of the clipboard when you change worksheets (since in the workshet_activate event, it does stuff that clears the clipboard) but now when the user pastes the data, the relevant worksheet_change event doesn't happen.

Application.enableevents is definitely true, and other events happen - just nothing after the paste.

Does anybody have any suggestions whatsoever? I'd like to keep the paste functionality if at all possible...

Cheers!

 

[jimboy]

I've noticed that the first line of you code is;

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

Try deleting the code and right mouse over the sheet name, select view code and paste this in;

Private Sub Worksheet_Change(ByVal Target As Range)
'Your code here
End Sub

 

[Chunder]

Hi Jimboy,

Neither of the two Sheet Change events operate after the paste event:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

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

I don't have the following anywhere:

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

- this looks like a custom event handler, based on a Dim App as New Application statement? Should I actually be using one of these instead, rather than the built in object events?

TVMIA!

 

[Chunder]

Private Sub Worksheet_Change(ByVal Target As Range)
'Your code here
End Sub

Yep - that's exactly what I've got, but it only operates if a user directly edits a cell...

When values are entered directly into a cell, the Worksheet_Change event is triggered (where my code is), followed by the Workbook_SheetChange event.

Update: Ack! I've just been playing around, and it does work when you copy and paste data on the same sheet, but as soon as you move between sheets, it doesn't happen!? Right - I'm going to post some code... brace yourselves