Excel freezes when pasting to “event procedure” cells


Posted by Celia on February 03, 2000 2:14 AM

I have a problem in that Excel freezes whenever data is pasted to workbook cells that are the subject of the event procedure “Worksheet_Change”. There is no problem if the data is input with the keyboard.
Can anyone help?
Is it possible to disable the event procedure if the cell value change is caused by a paste instruction?

Posted by Chris on February 03, 2000 5:34 AM

Celia,

Prior to pasting with the macro, disable the event procedure using:

Application.EnableEvents = false

Then turn it back on after the paste using:

Application.EnableEvents = true

HTH,
Chris

Posted by Celia on February 03, 2000 12:51 PM

Chris

Thanks, but the pasting is not done by the macro. What I needed was whether there is any code that can do this :-

If (user selects paste or paste special) Then
Application.EnableEvents = False
End if

Celia

Posted by Ivan Moala on February 03, 2000 4:08 PM

Hi Celia
Not 100% sure what you want to do but....
the following will assign the macro Macro1 to
the Paste button & the Paste special button,
so that if the user clicks either of these it
will run Macro1 which disables the event proc.
then pastes ??(do you want this) from the clipboard.

Sub menuItem_Create()
Dim PasteSpec

Set PasteSpec = CommandBars("Worksheet Menu Bar").Controls("Edit")
PasteSpec.Controls("Paste special...").OnAction = "Macro1"
CommandBars("Standard").Controls("Paste").OnAction = "Macro1"

End Sub

Sub Macro1()

Application.EnableEvents = False
ActiveSheet.Paste
Application.EnableEvents = True

End Sub

NB:user can still copy and paste by using the
copy then pressing ENTER key ?!


HTH

Ivan




Posted by Celia on February 03, 2000 7:35 PM

Ivan
Thanks for the input. I’ve tried various alternatives based on your suggestion but still can’t work it out.
I have the macro shown below. The “InsertMCC” macro looks up some databases and if it matches the “Selection”, various other fields are added to the worksheet.
It works as long as the user inputs data to the “Selection” with the keyboard, but everything freezes if any attempt is made to paste data to the selection.
It can be unfrozen by Ctrl+Alt+Delete and then selecting “End Task” and then, when prompted to save, selecting “Cancel”.
After this, the workbook unfreezes, remains open, and completes the macro (i.e. it runs “InsertMCC” and moves to the next selection).
Perhaps I’ll just have to live with it (or avoid pasting data to the “Selection” cells).
Celia

Private Sub Worksheet_Change(ByVal Target As Range)
If Selection.Column = 5 And Selection.Row > 17 And _
Selection.Row < Range("E65536").End(xlUp).Offset(-1, 0).Row Then
Application.Run "Personal.xls!InsertMCC"
Selection.Offset(0, 1).Select
End If
End Sub