excel - Inconsistent Results with PutInClipboard Method

Question

I have a spreadsheet set up with large text entries stored for selective copy-and-paste into another application; I am trying to devise a simple way for the user to (selectively) copy the text from any cell into the Clipboard, so that they can easily paste it into another application; all the while protecting the integrity of the source material.

The following code WORKS as desired:

 **Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     Dim OutputObject    As DataObject
     Dim CellText        As String

     CellText = Target.Text
     Set OutputObject = New DataObject
     OutputObject.SetText CellText
     OutputObject.PutInClipboard

 End Sub**

However, the result of the double-click leaves the cellpointer inside the cell (in EDIT mode), which puts the contents of the cell at risk of being overwritten. Inserting "CANCEL = True" should solve the problem by cancelling the effects of the double-click, leaving the cell SELECTED but not in EDIT mode. However this results in the Clipboard being empty as nothing pastes into the outside application (Notepad).

The exact same code -- for some inexplicable reason -- DOES NOT work as expected within the Worksheet_SelectionChange(ByVal Target As Range) subroutine!?!?!

I have also tried creating an ActiveX button on the spreadsheet; the button uses relative direction logic to determine which cell's text to copy, but AGAIN ... nothing pastes into the outside application (Notepad).

Any thoughts?

question from:https://stackoverflow.com/questions/65889795/inconsistent-results-with-putinclipboard-method

Answer 1

Using SendKeys in an Event Procedure

• SendKeys statement
• You could add the following to your Worksheet BeforeDoubleClick event code.

The Code

 With Application
     .ScreenUpdating = False
     .SendKeys "{ENTER}{UP}"
     .ScreenUpdating = True
 End With