SheetChange event doesn't fire when using paste

C

clu82

New Member
Joined
Aug 18, 2002
Messages
8
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
 
Sort by date Sort by votes

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.

Code: 
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.

Code: 
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. :oops:

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 :biggrin:
 
Upvote 0
I did a test with this code:

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:

Code: 
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.
 
Upvote 0
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!

Code: 
Set Sarcasm = True
I didn't know that any Microsoft software had any bugs in them; silly me...
Code: 
Set Sacrasm = False

Should'a guessed, shouldn't I? :rolleyes:
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
textBox AfterUpdate firing unexpectedly
Replies
3
Views
135
RoryA
RoryA
U
  • Question
How to using Function on VBA
Replies
1
Views
136
upl365
U
N
  • Solved
VBA Question
Replies
3
Views
124
RoryA
RoryA
B
  • Question
Event code to give message box and change color of auto-shape
Replies
4
Views
149
bh24524
B
C
  • Question
VBA copy paste currentRegion historically to another worksheet
Replies
5
Views
175
Alex Blakenburg
A

Forum statistics

Threads
1,214,792
Messages
6,121,612
Members
449,038
Latest member
apwr

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top