Worksheet_Change Event - Macro kills copy and paste

[Alan]

Hi All,

I have a macro running upon a Worksheet_Change and Worksheet_SelectionChange
event from within a worksheet code (not a module).

When a change is made to the worksheet, which is a filtered list, it calls a
sub which checks which columns are filtered and changes the background
colour to make it easier to see, or changes it back to no fill if the filter
was removed.

However, if a user tries to copy and paste from one cell to another, when
they move the cell pointer up a cell (say), one of the events is triggered
and by the time the macro has run, the contents of the cell that was copied
has been lost and paste is ineffective.

I am wondering if there is some way for me to check in the event procedure
whether the user has tried to copy something, and if so, then not call the
macro?

Thanks in advance for any help you can render,

Alan.

 

[Rob Bovey]

Hi Alan,

The value of Application.CutCopyMode will tell you whether a user has a
cut or copy operation in progress when your event procedure fires, something
like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CutCopyMode = 0 Then
''' User hasn't copied anything,
''' run your macro here.
End If
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Jerry Park]

Hi Alan,

The value of Application.CutCopyMode will tell you whether a user has a
cut or copy operation in progress when your event procedure fires, something
like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CutCopyMode = 0 Then
''' User hasn't copied anything,
''' run your macro here.
End If
End Sub

Thanks. That is good to know. Though I haven't had any problem with the
change event and copy/pasting, I have needed to know when a copy/paste
operation happened to clear comments and to speed up some functions.
Have been using this:

' If value was pasted, delete comments.
If ((ActiveCell.Row = Target.Row) And (ActiveCell.Column =
Target.Column)) Then
Target.ClearComments
IsCopyPaste = True
End If

 

[Miss Kim]

Rob,

Alan said the same code is the Worksheet_SelectionChange
event, so that will still cause the code to exacute once:
after the selection has changed, but before the Copy
command is initiated. I don't think it will be a problem
with Alan's code that only applies formating, but maybe a
better solution is to trap the filter change?

Any thoughts?

 

[Rob Bovey]

Rob,

Alan said the same code is the Worksheet_SelectionChange
event, so that will still cause the code to exacute once:
after the selection has changed, but before the Copy
command is initiated. I don't think it will be a problem
with Alan's code that only applies formating, but maybe a
better solution is to trap the filter change?

Hi Kim,

I just provided the Change event code as an example. The same kind of
check would have to be done in SelectionChange as well. Trapping the filter
change itself could be a good alternative, although it would require some
indirect programming. Because changing the autofilter criteria on a
worksheet doesn't fire any events, you'd have to do something like add a
volatile function to the worksheet, trap the Calculate event, and check for
a change in the filter criteria. This process itself would still need to be
prevented from inadvertently canceling a user's copy operation too, though.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Alan]

Hi Alan,

The value of Application.CutCopyMode will tell you whether a user
has a cut or copy operation in progress when your event procedure
fires, something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CutCopyMode = 0 Then
''' User hasn't copied anything,
''' run your macro here.
End If
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Hi Rob,

Your code did the job perfectly.

However - once I made that change, I noticed that the less excel literate
users were filtering the list, and copying down through filtered rows, thus
trashing everything in the 'hidden filtered' rows that are not shown.

I should have anticipated this, but due to the 'bug' that you helped me
resolve, it had not been an issue due to the 'disabling' of the copy / paste
functionality.

Therefore, I have actually removed the code that you supplied, and returned
it back to the original position.

The 'bug' have now been re-designated as a 'feature'!!

Thanks anyway - I learnt something if nothing else.

Regards,

Alan.