-1

When I have something on my clipboard, and I have a change event macro that runs, I'd like to be able to trap what's in the clipboard at the start of the macro executing, then when the macro is complete, put it back in the clipboard. Otherwise, if I copy and paste before my macro executes, my clipboard automatically clears when the change macro runs, and I can't copy multiple times from clipboard.

I've tried inserting this code into my change event, but I get a "Compile error: user-defined type not defined" error every time.

Dim Clipboard As MSForms.DataObject
Set Clipboard = New MSForms.DataObject

Clipboard.GetFromClipboard

Clipboard.PutInClipboard

I've tried DIMing as just a DataObject, or even just an Object, but still the same error.

Gary Nolan
  • 111
  • 10
  • Please read the guidance on how to ask a question on SO. – freeflow Apr 25 '23 at 15:27
  • @FreeFlow I'm not sure how better to ask my question. But I've edited it, with a code sample, if this helps. – Gary Nolan Apr 25 '23 at 15:53
  • 1
    If you add a userform to your project it will add the required reference. – Tim Williams Apr 25 '23 at 16:09
  • 1
    ...but I think your problem may be that the clipboard gets cleared before any code in your event procedure runs: you can put `Debug.Print Application.CutCopyMode` at the top of the procedure and it will always be 0. – Tim Williams Apr 25 '23 at 16:24
  • 1
    @BigBen - I'm OK with re-opening but I'm not sure there's any solution to this - it's a long-standing side-effect of using events in VBA. – Tim Williams Apr 25 '23 at 16:37
  • Thanks, Tim. You've been a big help yet again. I added a userform, which then added the Forms 2.0, and the code worked fine. I deleted the userform, and it didn't remove that Forms 2.0 library, so it still worked fine. – Gary Nolan Apr 25 '23 at 16:51
  • @TimWilliams I'm getting an error at the end of my sub now. First parts where I'm adding the clipboard to variable are OK, but at the end of the code where I'm using "PutInClipboard" is now throwing Run-time error -2147467263 (80004001): DataObject:PutInClipboard Not Implemented. Seemed related to an empty clipboard, so I modified the line to 'If IsEmpty(Clipboard) = False Then Clipboard.PutInClipboard' but I still get the error. It's when I'm changing a cells value, so the change event runs, but there isn't anything in my clipboard. When there is something in my clipboard, it seems OK. – Gary Nolan Apr 25 '23 at 17:04
  • Like I mentioned in a previous comment - I think the clipboard is cleared before your event code even runs. – Tim Williams Apr 25 '23 at 17:07
  • Maybe I'm misunderstanding you. If there is something in my clipboard before the change event runs, the macro does not fail. I can copy what was in my clipboard after the macro runs. So I'm assuming it doesn't clear it. I get this error when there is nothing in my clipboard before the macro runs, which makes me think it takes issue with running this code on an empty clipboard. – Gary Nolan Apr 25 '23 at 17:46
  • @gary I think the answer to this will depend on what is on the clipboard, and where it came from. Please update your Q to share that info – chris neilsen Apr 25 '23 at 20:49

2 Answers2

0

I'd guess you mean to do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim Clipboard As MSForms.DataObject
    
    Debug.Print Target.Address, Application.CutCopyMode 'any range on clipboard?
    
    Set Clipboard = New MSForms.DataObject
    
    Clipboard.GetFromClipboard
    
    'do something in your macro
    
    'restore the clipboard
    If Application.CutCopyMode <> False Then Clipboard.PutInClipboard

End Sub

What I see in my testing is

  • if you enter data by typing in a cell, then CutCopyMode is always zero before your code even does anything, and any range on the clipboard is cleared.
  • if you paste a copied range on the sheet and the event is triggered, CutCopyMode is 1 (and you can repeatedly paste without the clipboard getting cleared)
  • if a paste triggers a cell update in the event handler, that doesn't clear the clipboard

If would help if you could outline a complete use case, along with a full event handler code to replicate the issue you're trying to solve.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
0

I've found one way to effectively do what I want here. At the beginning of my change event, I add this code:

Dim Clipboard As Integer
Clipboard = Application.CutCopyMode

If I haven't copied anything, my Clipboard variable will be a zero. If I have copied something, it will be a 1.

Since this is a change event, if I copied and pasted to trigger it, my active cell is what I had on my clipboard.

So at the end, I have this code that says if my clipboard variable is 1, it'll copy the cell I just pasted to. So it isn't copying the original cell I copied, but it at least puts whatever was on my clipboard back on my clipboard if I had indeed copied beforehand.

If Clipboard = 1 Then ActiveCell.Copy
Gary Nolan
  • 111
  • 10