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

Create Account

Microsoft Excel

Questions

Followers

Top Experts

Robert Berke🇺🇸

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

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

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!

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

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 a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

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🇬🇧

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

ASKER CERTIFIED SOLUTION

Robert Berke🇺🇸

ASKER

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

SOLUTION

aikimark🇺🇸

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

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.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

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 a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

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.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

SOLUTION

Chris Bottomley🇬🇧

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

Robert Berke🇺🇸

ASKER

my post 36357998 has the "best solution"

Microsoft Excel

Questions

Followers

Top Experts

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.