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

 

[Andrew Poulsom]

If your code clears the Clipboard, how are you enabling the user to paste?

 

[Chunder]

Firstly, this should take the contents of the clipboard, same them to the dataobject, manipulate the sheet that has just been selected (to ensure that some data that gets hidden elsewhere is actually visible; sets the font colour to white) and then puts the dataobject data back onto the clipboard.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

clipdata = GetOffClipboard

wsheet = Sh.Name
If wsheet <> "Agent Details" And wsheet <> "Agent Import" Then
    Call show_area(wsheet)
End If

PutOnClipboard (clipdata)

Application.EnableEvents = True

End Sub

(Note that this makes use of the procedures published at http://www.cpearson.com/excel/clipboar.htm)

OK - that's fine, and all seems to work perfectly; I can copy and paste between sheets. There's no Application.Enablevents statements in sight (apart from the obvious, just-in-case one!) in any of the procedures that get called along the way.

Now, the following code is used to update the worksheet that has just been changed, and to populate further details based on the value that was entered.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    
Call update_worksheet(Target)

End Sub

If I set a breakpoint on the Call statement, I can see the code running to it when I edit a cell directly, or when I paste data from the same worksheet.
If, however, I copy data from a different worksheet, and paste it into the sheet with the Worksheet_Change event shown above, then it (the event)doesn't get triggered.

The only thing that I can think is causing the problem is the manipulation of the clipboard... but I can't see anything amiss

Help me Obi-Wan Kenobi, you're my only hope

 

[Andrew Poulsom]

I did a test with this code:

'ThisWorkbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim clipdata
    clipdata = GetOffClipboard
    Application.CutCopyMode = False
    PutOnClipboard (clipdata)
    Application.EnableEvents = True
End Sub

'Sheet2 module

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

'General module

Public Sub PutOnClipboard(Obj As Variant)
    Dim MyDataObj As New DataObject
    MyDataObj.SetText Format(Obj)
    MyDataObj.PutInClipboard
End Sub

Public Function GetOffClipboard() As Variant
    Dim MyDataObj As New DataObject
    MyDataObj.GetFromClipboard
    GetOffClipboard = MyDataObj.GetText()
End Function

It works just fine in Excel 2000, but sure enough the Worksheet_Change event does not fire in Excel 97. So it must be a bug.

The only alternative I can think of is something like this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim Rng As Range
    If Application.CutCopyMode = 1 Then
        Worksheets("Temp").Select
        ActiveSheet.Range("A1").Select
        Worksheets("Temp").Paste
        Set Rng = ActiveWindow.Selection
        Application.EnableEvents = False
        Sh.Activate
        Application.EnableEvents = True
    End If
'   Replace next line with your code
    Application.CutCopyMode = False
    If Not Rng Is Nothing Then
        Rng.Copy
    End If
End Sub

which copies the contents of the Clipboard to a sheet named Temp (if necessary) and puts it back on the Clipboard from there. You could Hide/Unhide that worksheet in your code if you want.

 

[Chunder]

Ahhhhh - thanks for that! It's nice to know that I'm not really going mad

I'll have a play around with your proposed work around; it's probably going to be one of those things that never, ever, ever happens, so perhaps I'll just put a custom commandbar button that forces a recalculation of the sheet...

Many thanks for all your help!

Set Sarcasm = True

I didn't know that any Microsoft software had any bugs in them; silly me...

Set Sacrasm = False

Should'a guessed, shouldn't I?