0

due to code that's running in the background when you open a Worksheet (Worksheet_Activate), contents on the clipboard get lost.

I was able to store the contents of the clipboard before the regular code runs and give it back via

Function readClipboard() As String
'Tools -> References -> Microsoft Forms 2.0 Object Library
'if you get a "Compile error: user-defined type not defined"
    DataObj.GetFromClipboard
    On Error GoTo notText
    readClipboard = DataObj.GetText
    Exit Function
notText:
    readClipboard = ""
End Function

Function copyToClipboard(ByRef copy As String)
    If Not copy = "" Then
        DataObj.SetText copy
        DataObj.PutInClipboard
    End If
End Function

With this version however the borders get pasted as well, which I would like do avoid. When you copy and paste things in Excel you can choose not to paste the border. I used "record makro" to see how it would look like within the code

    Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Is there a way where I can delete the border formating when I get the data from or to the clipboard?

Thanks in advance

Lazmancı
  • 1
  • When you copy a range in Excel what gets put on the clipboard is different from when you set the clipboard *text only* (which is what you're doing here), so you may not be able to return the clipboard to its original state. – Tim Williams Apr 15 '23 at 16:14
  • If you need to find out what range was on the clipboard so you can re-copy it, there's a solution here - https://stackoverflow.com/questions/23112161/retrieve-location-of-copied-cell-range-in-vba – Tim Williams Apr 15 '23 at 16:21

0 Answers0