CellDragAndDrop & Paste buffer

Jon von der Heyden said:

Hello BG

Perhaps you should disable CellDragAndDrop on sheet activate. Otherwise assuming that what is in cut / copy mode is still the selection, perhaps;

Code:

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] phoo1()
    [COLOR="Blue"]Dim[/COLOR] xlCutCopy [COLOR="Blue"]As[/COLOR] XlCutCopyMode
    xlCutCopy = Application.CutCopyMode
    
    Application.CellDragAndDrop = [COLOR="Blue"]False[/COLOR]
    
    [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] xlCutCopy [COLOR="Blue"]Then[/COLOR]
        [COLOR="Blue"]Select[/COLOR] [COLOR="Blue"]Case[/COLOR] xlCutCopy
            [COLOR="Blue"]Case[/COLOR] xlCopy: Selection.Copy
            [COLOR="Blue"]Case[/COLOR] xlCut: Selection.Cut
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Select[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

Click to expand...

Jon von der Heyden said:

Do you want to disable the CellDragAndDrop for the entire workbook? If so you could disable it on the Workbook_Open and Workbook_BeforeClose event. Of course this is an application property so this will affect other workbooks too. But if you code it to allow CellDragAndDrop using the Workbook_Deactive event, and disallow on Workbook_Activate, then you have a similar issue in that users cannot copy data from another open workbook. But at least using these events increases the scope that your users can copy/paste within.

Click to expand...

Jon von der Heyden said:

I have one last suggestion up my sleeve but again it's not a complete solution.

Requires reference to Microsoft Forms Object Library:

Code:

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Worksheet_Activate()
    [COLOR="Blue"]Dim[/COLOR] objData [COLOR="Blue"]As[/COLOR] DataObject
    [COLOR="Blue"]Dim[/COLOR] xlCutCopy [COLOR="Blue"]As[/COLOR] XlCutCopyMode
    [COLOR="Blue"]Dim[/COLOR] blnPutInClipboard [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Boolean[/COLOR]
    
    xlCutCopy = Application.CutCopyMode
    [COLOR="Blue"]Set[/COLOR] objData = [COLOR="Blue"]New[/COLOR] DataObject
    
    [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] xlCutCopy [COLOR="Blue"]Then[/COLOR]
        objData.GetFromClipboard
        blnPutInClipboard = [COLOR="Blue"]True[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    
    Application.CellDragAndDrop = [COLOR="Blue"]False[/COLOR]
        
    [COLOR="Blue"]If[/COLOR] blnPutInClipboard [COLOR="Blue"]Then[/COLOR]
        objData.PutInClipboard
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

This will allow you to paste whatever was copied, but it will not paste formula and it will not let you use the conventional Paste Special (i.e. formats / validation / multiple etc...).

Click to expand...

BallGazer said:

Do I have to do anything special to use the Microsoft Forms Object Library ?

Click to expand...

BallGazer said:

Generally all that is needed is paste Values.

Click to expand...

Jon von der Heyden said:

In the VBE go Tools > References and check Microsoft Forms xxx Object library. xxx refers to the version (i.e. a #).

Aother way is just to add a userform to your project. You don't need to bother if you already have a userform because it will already be there then.

EDIT:

You may want to think twice about using this method then, because PasteSpecial Values isn't an option. Although values will be pasted (i.e. no formula), it will carry the format with it.
Post back if that's a problem - I think we can put only text in clipboard (but don't know for sure, will need to have a play)...

Click to expand...