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.
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 only way which I have found to overcome this problem is indeed simple. It does not use complex calls to Windows APIs as some websites suggest. Because my only concern was in keeping any String type Clipboard data, I save the contents of the Clipboard 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
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 sub routine 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.
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
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.