Saving your Clipboard Data after an Excel “Copy” with VBA

You may notice that Excel’s Clipboard data has been changed (or purged) after using one of its many copy methods. Then again, this issue may have slipped past you while testing your code. This situation, though annoying, is quite understandable.

The Problem…

In many of my Excel VBA macros, rows are added using the Range Copy method, as shown in the following code snippet:

    NewRow = Sheet1.Range("Q2") + 1
    newLastLine = (NewRow + numLines) - 1
    
    Sheets("BidSheet_Template").Rows(17).Copy

In this example code, the new rows are copied from a hidden “template” worksheet. The variable NewRow is set to the next row down from the ActiveCell.Row, which is saved in a hidden column’s cell, “Q2”. For this workbook, every time a user moves to another cell Sheet1.Range (“Q2”) is updated. The variable newLastLine is used to account for inserting multiple rows by adding the value of the variable numLines. That variable comes from the form, which allows the user to select adding 1 or multiple new rows. It is the number of lines to add.

So much for the really inconsequential code. What matters comes next, the Copy method. Many times a user will copy some text and want to insert it in a new row, I know that I do. The issue here is that once the VBA Copy method is called, the clipboard overwrites the previous data with the “copied” data. In the previous code, the copied row(s).

The Fix…

The only way I have found to overcome this problem is indeed simple. It does not use complex calls to Windows APIs as some websites suggest. To use the following method you must have the Microsoft Forms 2.0 Object library (FM20.dll) referenced in the project.

Because my only concern was keeping any String-type Clipboard data, I save the Clipboard’s contents to a string. To be safe or to capture data other than simple strings, you could use VBA’s Variant type.

I fixed this problem by creating 2 short routines. The first one is a User Defined Function (UDF) that sets a variable to the current clipboard data, Clipboard_GetText():

Public Function Clipboard_GetText()
' Save the value in the clipboard to restore after a "copy" routine...

Dim objData As DataObject

    Set objData = New DataObject
    
    objData.GetFromClipboard
    Clipboard_GetText = objData.GetText

    Set objData = Nothing

End Function

The second routine is a Sub procedure, Clipboard_SetText that simply sets the current clipboard data to the variable saved using the function. It takes the saved variable as an argument and sets it to the contents of the Clipboard:

Public Sub Clipboard_SetText(SavedText As String)
' Set the Clipboard text to the value of the String argument, to be used after the function Clipboard_GetText()

Dim objData As DataObject
    
    Set objData = New DataObject
    
    objData.SetText SavedText
    objData.PutInClipboard

    Set objData = Nothing

End Sub

The Implementation…

Once you have the Clipboard_GetText function and the Clipboard_SetText sub-procedure coded, the application is easy. This code works whether a user has copied anything to the Clipboard or not. It can be included, just in case a user wants to paste copied text. The four steps are:

  • Declare a String variable, named however you wish (the following example uses the name “strSavedClip”)
  • Use the function to Set the String variable to the current Clipboard data
  • Run your “copy” code…
  • Call the subroutine to reset the Clipboard data to the saved value

This is how the code should be arranged…your code will look somewhat different, especially since the 2 Calls often may not appear next to each other.

' Save the current clipboard data
strSavedClip = Clipboard_GetText()

'---> ADD your copying code here

' Reset the saved clipboard data
 Call Clipboard_SetText(strSavedClip)

This is a simple solution that can be used over and over again throughout your project.

Remember that, using “Application.CutCopyMode = False” will also clear Excel’s recently copied data from the Clipboard. You should avoid setting this property when possible.

10 thoughts on “Saving your Clipboard Data after an Excel “Copy” with VBA

  1. This method does not work on my computer (Windows 10, Office 365 64-bit).

    The issue is with the Clipboard_SetText sub.

    objData.PutInClipboard puts ?? into the clipboard when utilizing debug.print to see the value and pastes into a cell as ￿￿

    • Jim,
      I have had that issue, and I can’t recall what was the problem. However, the bugs were worked out in the BidSmart Estimator which you can download for free on my website. If you search the VBA source code for “Clipboard_SetText” you will see how I used it successfully there. Download here–>https://rlbcontractor.com/bidsmart-estimator

      I am up to my ears in another project and can’t get away to look at it right now, but the BS Estimator works.

      –Rick

  2. Hi, thanks for the example.
    I get the error ‘User-defined type not defined’ on the start of the GetText function.
    Googling around didn’t help. Should I activate some library for this to work?
    Thanks in advance

    • Hi Bart,

      I forgot where I found this workaround, but it wasn’t my idea. Add “On Error Resume Next” before the code that calls GetText(). Such as:

      Public Sub Clipboard_SetText(SavedText As String)
      ‘ Set the Clipboard text to the value of the String argument, to be used after the function Clipboard_GetText()
      Dim objData As DataObject

      On Error Resume Next

      Set objData = New DataObject

      objData.SetText SavedText
      objData.PutInClipboard

      Set objData = Nothing

      Exit Sub

      End Sub

      I hope this fixes it for you.
      -Rick

  3. Thank you, Rick!

    But I still get the ‘compile error: User-defined type not defined’ at the Dim statement. Even if I move the On Error line above that Dim statement.
    I think that the On Error would catch run-time errors during the GetText call, but my problem occurs before then.

    Cheers
    Bart

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.