CellDragAndDrop & Paste buffer

[BallGazer]

Hi Guys

Does anyone know how I can disable Drag/drop without emptying the clipboard on sheet/workbook change?

Any help greatly appreciated.

Regards

BG

 

[Jon von der Heyden]

Hello BG

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

[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]

 

[BallGazer]

Hello BG

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

[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]

Hi Jon

I currently disable CellDragAndDrop on sheet Activate. However when copying from another sheet or workbook the sheet_activate event procedure empties the clipboard. So far I can't find a way round this.

Thanks for your help

BG

 

[Jon von der Heyden]

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.

 

[BallGazer]

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.

Jon

Thank you for your suggestion. Unfortunately I need Drag/Drop enabled on most of the other sheets. I could adopt your suggestion and activate on all the other sheets. However I am approching 50 sheets now and this is quite a task.

By careful use of the change event I can get close to what I need. Drag/Drop becomes disabled after the first change. As long as the user doesn't make an illegal "drag" the first change I can avoid them screwing up the logic on this sheet. Its far from ideal but does work somewhat.

Once again many thanks

BG

 

[Jon von der Heyden]

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

Requires reference to Microsoft Forms Object Library:

[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...).

 

[BallGazer]

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

Requires reference to Microsoft Forms Object Library:

[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...).

Jon

Many thanks. FYI - the last thing I want is for the users to paste formulas! Generally all that is needed is paste Values.

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

Regards

BG

 

[Jon von der Heyden]

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

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:

Generally all that is needed is paste Values.

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)...

 

[BallGazer]

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)...

Hi Jon

Thank you for your latest suggestion. I have decided to go with my solution which only exposes the drag/drop prior to first change. I think this will stop 99.99% of user errors. However I do have users in Moscow who are pretty good at plain mistakes plus my office in Dubai seems to employ PhDs from India who like nothing better than to break the model!! As I say I will leave this part for now. I really appreciate your time and interest.

BG

 

[hadihakimzadeh]

hi,
i know this is an old post but i still need a solution to disable Drag/drop without emptying the clipboard!
i have a workbook that i want to disable drag/drop and be able to copy to/from my workbook.
(sorry for bad english)