Avatar of Robert Berke
Robert Berke
Flag 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/Q_27251773.html#a36356176
Microsoft Excel

Avatar of undefined
Last Comment
Robert Berke

8/22/2022 - Mon
Chris Bottomley

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
Chris Bottomley

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

Chris Bottomley

Not selected range but the copy buffer ... but hopefully you realised that!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Chris Bottomley

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

Rory Archibald

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

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Bottomley

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
aikimark

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
Rory Archibald

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rory Archibald

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
aikimark

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?
Chris Bottomley

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

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.  
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Bottomley

I thought the selectionchange event handler would do it cleanly ho hum!

Chris
Robert Berke

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

Robert Berke

ASKER
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Robert Berke

ASKER
my post 36357998 has the "best solution"