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.

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 *


"Alan" wrote in message
...

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 wrote:
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

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?

-----Original Message-----
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 *


"Alan" wrote in message
...

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.





.

"Miss Kim" wrote in message
...
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 *

"Rob Bovey" wrote in message
...

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.