Link to home
Start Free TrialLog in
Avatar of Robert Berke
Robert BerkeFlag for United States of America

asked on

vba to retrieve range of cells that were put on the clipboard with ctrl C

I assure you this is a difficult question, so read it carefully before attempting to help.

Please, don't point me to any "rookie programmer links" or "sample clipboard code links".  I am already fairly expert at VBA and have spent hours looking at links.  I found a few that addressed this exact problem but those experts concluded that it can't be done within the Excel Object Module.  

The user selects several cells and does a Ctrl C, then selects another cell anywhere they want and then call my macro, PasteSpecialSum which inserts formula '=sum([book.xls]sheet1!a1:a5,...)' into the target cell.

PasteSpecialSum retrieves the users range off the clipboard which I discovered was very difficult.  I developed the attached ReselectClip code to solve the problem. But it is complicated, so the goal of this questions is to come up with a better way.

I do NOT want the user to call my macro first, so please do not suggest code like
    set DesiredRange = inputbox( "Please select desired cells" ....type:=8)

I also do NOT want my user to select the destination first. The ctrl C must be done first.

Also, I can't use events because the user might switch to a cell in another workbook, then call PasteSpecialSum. And they might select various cells before deciding where to paste it.  This means I cannot easily use events like Selection_change.

NOTE: Code posted below is not the correct code for this issue. Please see this comment for the correct code: https://www.experts-exchange.com/questions/27251773/vba-to-retrieve-range-of-cells-that-were-put-on-the-clipboard-with-ctrl-C.html?anchorAnswerId=36356176#a36356176
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

You talk in terms of simply getting the text as in the buffer so initially

Dim cb as msforms.data object
Cb.getfromclipboard
Debug.print cb.text

Chris
ALso, not that I think this is what you asked for ... but the following sets up an array of the data in the selected range:

Chris
Sub cutandPasteTest()
Dim CB As New MSForms.DataObject
Dim rng As Range
Dim var As Variant
Dim rw As Variant
Dim intRows As Integer
Dim intCols As Integer
Dim processRow As Integer
Dim processCol As Integer

    CB.GetFromClipboard
    var = CB.GetText
    rw = Split(var, vbCrLf)
    intRows = UBound(rw)
    intCols = UBound(Split(rw(0), vbTab)) + 1
    ReDim var(1 To intRows, 1 To intCols)
    For processRow = 1 To intRows
        For processCol = 1 To intCols
            var(processRow, processCol) = Split(rw(processRow - 1), vbTab)(processCol - 1)
        Next
    Next
    
End Sub

Open in new window

Not selected range but the copy buffer ... but hopefully you realised that!
Interestingly that latter code errors if the paste buffer is empty so this fixes that ... just for accuracy.

Chris
Sub cutAndPasteTest_2()
Dim CB As New MSForms.DataObject
Dim rng As Range
Dim var As Variant
Dim rw As Variant
Dim intRows As Integer
Dim intCols As Integer
Dim processRow As Integer
Dim processCol As Integer

    On Error Resume Next
    CB.GetFromClipboard
    var = CB.GetText
    If var = "" Then Exit Sub
    On Error GoTo 0
    rw = Split(var, vbCrLf)
    intRows = UBound(rw)
    intCols = UBound(Split(rw(0), vbTab)) + 1
    ReDim var(1 To intRows, 1 To intCols)
    For processRow = 1 To intRows
        For processCol = 1 To intCols
            var(processRow, processCol) = Split(rw(processRow - 1), vbTab)(processCol - 1)
        Next
    Next
    
End Sub

Open in new window

FWIW, I don't think that any of the information copied to the clipboard contains the address information you want. I suspect you would have to intercept the copying when it happens rather than trying to get the information afterwards.
I think the easiest solution would be for you to look at the current SELECTION.  It isn't foolproof, since someone might copy cells to the clipboard and change the selection before your code gets control.
aikimark

"The user selects several cells and does a Ctrl C, then selects another cell" so the current selection cannot be used for the purpose ;o)

Chris
If the macro is called before the destination cell, then the macro could use the
Application.InputBox() method specifying the type of result as range to get the destination for the paste (or even Copy() method).
SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm still not convinced the address information is actually on the clipboard rather than being held internally by Excel (cf the marching ants). It certainly doesn't appear to be on the Office clipboard as you can't paste link from there.
I looked for the 'moving border' too and didn't find it. :-(

=========
@rberke

Can this pastespecialsum() macro paste the sum or must it paste the Sum() formula?
Okay how about a mod to each worksheet you are interested in, to call a function that saves the address.  Take the sub "Worksheet_SelectionChange" and place it in teh worksheet(s).

Now take the rest and place it into a new code module.  In your sub you should now be able to access the previous selection as wasrange.  It will however only be relevant when cut/copy mode has beem activated ... which is I believe the situation here.

Chris
' Worksheet code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    WS_SelectionChange Target
End Sub

'Normal Code Module
Public isRange As Range
Public wasRange As Range

Public Sub WS_SelectionChange(ByVal Target As Excel.Range)

    Set isRange = Selection
    If Application.CutCopyMode = 0 Then
        Set wasRange = isRange
     End If
     
End Sub

Open in new window

Avatar of Robert Berke

ASKER

Tomorrow I will soon put everybody out of their misery and close this problem.  

I will award points to everyone because of their effort, but I will mark my own post 36357998 as the "Accepted Answer".

My routines may be very ugly, but the do exactly what I want.  They is entirely Excel vba with no reference to APIs.  To test it, people can cut and paste it into a standard module then assign short cut key like ctrl shift P  to the Test macro.  

select cells a1, a3, a5 > ctrl c to clip board >  select cell C1 > ctlr shift P

you will paste the formula "=sum(a1, a3, 15)" in C1.  
I thought the selectionchange event handler would do it cleanly ho hum!

Chris
By the way, I agree with Rory that the Excel clipboard is probably not quite the same as the Office Clipboard.  And the office Clipboard is not the same as the Windows clipboard.  

And, if there are several instances of Excel, each clipboard has a slightly different version.  

To demonstrate, put 1,2,3,4,5 into [a1:a5]

use control key to scatter select [a1,a3,a5]  and ctrl C to put it onto the Excel clipboard.

Paste it at cell c1 and you will paste 3 cells containing 1,3,5

Open a new instance of Excel and paste it to new workbook cell c1 and you will paste 5 cells 1,2,3,4,5.

But, for this question, the differences are irrelevant, because the only clipboard that I am concerned with is a single instance of the Excel clipboard.   That instance contains all the information I need, to interpret the Running Ants. The ReselectClip routine does everything I need.  It is just ugly.

Chris,

As you pointed out yourself, it is not foolproof.  My routine is much closer to being foolproof. Also, mine is a "generic" routine which I use in any workbook I am using.  Some days I use it a dozen times.

You selection change routine would have a lot less code, but it is also ugly in  different way.  It would require an event handler that was always active, so the code would be invoked every time I changed any selection in any workbook.  Surprisingly, this does not cause too much of a performance hit, but it is ugly.

I actually started off with that exact solution 5 years ago, and it worked.  The performance wasn't noticeably bad, but I found that the routine often failed when I wanted to paste the sum into a different worksheet in the same workbook.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
my post 36357998 has the "best solution"