9

I'll start by saying that my experience with Excel and VBA is limited to what I saw in school. I have programming experience, but in other languages.

I have a file that I get every week. The structure of this file is always the same: ID, Name, Date, Value between 1 and 4, non-relevant data.

This data is selected through the 'select all' button (top left corner of the worksheet, little triangle below the cellname in MS excel 2013) and then copied into another default file that reworks the data to show and filter it in different sheets based on the 1-4 value and the date.

My question: How do I detect when data has/is being pasted? I've tried the Worksheet.Change event, but the paste command (CTRL+V) does not trigger the Change event. Also, how will the data be copied? Will it update Row by row, cell by cell (which direction), ...? I know I can easily find the answer to the last question by debugging it once I can detect the copy command, but you never know if someone knows the answer.

Is there another, more easy (or better) way to do this?

More data and information can be given if needed.

Thank you for your help.

EDIT: '...has/is being copied?' changed to pasted as it should've been.

Nahbyr
  • 489
  • 2
  • 4
  • 17
  • Why would you like to know if data have been copied? Is there any specific reason? What do you want to achieve? Do you want to populate this data into another workbook/worksheet? – Maciej Los Jan 07 '15 at 11:54
  • Have you seen this: http://www.siddharthrout.com/2011/08/15/vba-excelallow-paste-special-only/ ? – Maciej Los Jan 07 '15 at 12:14
  • @MaciejLos The reason why is because the people who will use the file get a large list of data every week. They will paste this list into the first worksheet of the file I'm currently working on and I need the file to then filter the relevant data out and display it in other worksheets based on certain values per row. RE: your other comment: I had opened it before my break, but hadn't finished reading it. I'm currently trying it out and it appears to do what I want. Will post the answer if it does. – Nahbyr Jan 07 '15 at 12:55
  • @Nahbyr You say " I've tried the Worksheet.Change event, but the paste command (CTRL+V) does not trigger the Change event." It is deeply troubling if this is what your experience is. Wow! What kind of setup, version, mods, addons etc. do you have going there for this to be true?! – Gene Skuratovsky Jan 07 '15 at 14:39
  • @GeneSkuratovsky Apparently it did. I just didn't know how to check it. The answer I posted below is the solution to my problem, but I can't accept it yet (it says I need to wait 2 days). – Nahbyr Jan 07 '15 at 15:11

3 Answers3

16
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim UndoList As String

  '~~> Get the undo List to capture the last action performed by user
  UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

  '~~> Check if the last action was not a paste nor an autofill
  If Left(UndoList, 5) = "Paste" Then
    'Do stuff
  End If
End Sub

This did the trick. For those who need something similar and know the size of their list @MaciejLos' answer would also work.

Nahbyr
  • 489
  • 2
  • 4
  • 17
1

Worksheet_Change event will do the job if you add a formula into cell which will never be overwritten. Let's say your data are pasted into A1 cell and occupied 5 columns. So, enter below formula into 6. column and row 1.

=COUNTBLANK(A1:A1048576)

Now, you're able to handle/detect paste event ;)

Maciej Los
  • 8,468
  • 1
  • 20
  • 35
  • That would work if it weren't for the fact that I do not know how large the data list they'll import will be. I have found a fix for my problem in the link you sent earlier. Will post the code. – Nahbyr Jan 07 '15 at 14:14
1

I was unable to add this as a comment so I'm posting this as an answer. @Nahbyr 's answer works when excel has "English" set as it's preferred language, otherwise it won't work.

So after manually searching using the immediate window I was able to find out the appropiate indexes for it to work on every language.

This is the function I wrote to test if the last action was a paste action, paste or paste special.

Public Function LastActionPaste() As Boolean

' The function LastActionPaste checks if the last action made was a paste action, if so it returns TRUE
' Otherwise it returns FALSE

Dim UndoList As String

LastActionPaste = False
UndoList = Application.CommandBars(11).Controls(14).List(1)

'~~> Check if the last action was a paste or paste special
If UndoList = "Paste" Or UndoList = "Paste Special" Then
    
    LastActionPaste = True
    
End If

End Function

UPDATE

So apparently the indexes are not the same on different installations of Excel, whether because they are different versions or whatsoever...

So even if the preferrred language is not English, the CommandBars.Name is still in english, BUT the Controls.Caption do change...

Now I hope that the Controls indexes do not change otherwise this won't work.

So I modified the function like this for it to work:

Public Function LastActionPaste() As Boolean

' The function LastActionPaste checks if the last action made was a paste action, if so it returns TRUE
' Otherwise it returns FALSE

Dim UndoList As String
Dim barFound As Boolean
Dim index As Long

LastActionPaste = False
index = 1
barFound = False

Do While barFound = False
    
    If Application.CommandBars(index).name = "Standard" Then
        
        barFound = True
        
        Else
        
        index = index + 1
        
    End If
    
Loop

UndoList = Application.CommandBars(index).Controls(14).List(1)

'~~> Check if the last action was a paste or paste special
If UndoList = "Paste" Or UndoList = "Paste Special" Then
    
    LastActionPaste = True
    
End If

End Function
David
  • 38
  • 6