problem with Worksheet_SelectionChange

[Guest]

Dear experts,
I have the below code on the worksheet object of my workbook, to prevent
users to drag and drop cells in a certain range... it works, but it makes the
"copy and paste" functionality of my worksheet disappear!
Is there a way I can have this back?
Thanks,
best regards,
Valeria

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRange As Range
Set myRange = Range("ID_Conf")
If Application.Intersect(Target, myRange) Is Nothing Or Target.Text = "Y" Then
Application.CellDragAndDrop = True
Else
Application.CellDragAndDrop = False
End If

End Sub

 

[Tom Ogilvy]

Certain actions cause the clipboard to be cleared when a range is selected
and you have done Edit=>copy. This appears to be one of them.

 

[Guest]

Hi Tom,
is there something I can do to avoid this? Force the clipboard not to be
cleared, or use another method to disbale drag and drop?
Thanks,
best regrads,
Valeria

 

[Guest]

Tom,
I've just checked and the clipboard is not cleared after the event is
triggered - only the "paste" functionality disappears...
and it's whenever an "Application.CellDragAndDrop" is triggered, be it false
or true makes no difference!

Any ideas on how to avoid this?
Thanks!
Best regards,
Valeria

 

[Tom Ogilvy]

If there is something in the clipboard, then paste should be enabled. If
there isn't, then paste is disabled. I don't know what you have checked,
but if paste is disabled, the point is moot.

as far as working around it. I don't know of any method.

 

[Guest]

Hi Tom,
this is the strange point about this...
the clipboard is OK, but the "paste" button both on right click and on the
command bar is disabled, which means that if I click on the clipboard object
directly it works, any other method does not work.
Odd, isn't it? And it is caused in my Excel just by the below code ,even in
an empty workbook...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CellDragAndDrop = True
End Sub

If I could paste here an image I would do it to show you exactly... anyway,
thanks!
Best regards,
Valeria

 

[Tom Ogilvy]

If your talking about the Office Clipboard, I have no knowledge of that. My
comments were addressing the Windows clipboard which is usually what affects
the menus.

 

[Guest]

Hi Tom,
I always learn something, I did not even know that there were 2 different
clipboards!! And yes, I was talking about the Office Clipboard, the one you
can activate under the "edit" menu.
So the "application.celldraganddrop" empties the Windows clipboard and not
the Office one...
Thanks!
Best regards,
Valeria