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. Ive tried various alternatives based on your suggestion but still cant 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 Ill 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
