CODEWITHSUNDEEP
excelclipboardvba
MisterWeary
MisterWeary

Reputation: 605

Retaining clipboard content during code execution

This seems like it should be a simple problem but I've been unable to solve it. I have a program I'm writing where the user is babied through these steps:

Step 1: Go to another spreadsheet and copy the content
Step 2: Come back to my spreadsheet and press a button to paste that content

When they press my button it needs to unlock the current sheet and paste the data without any of the formatting. This is what I have:

ActiveWorkbook.ActiveSheet.Unprotect
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
                       Operation:=xlNone, _
                       SkipBlanks:=False, _
                       Transpose:=False

So it works perfectly if I don't do the "Unprotect" line, but I'm trying to stop people from pasting the data without using my button.

I know that the issue is something to do with clipboard content being lost when I unprotect the sheet but the methods I've used to retain that content aren't working, namely:

Curiously, it works perfectly if I debug the VBA and step through line by line?!?

Upvotes: 2

Views: 6361

Answers (2)

Daniel
Daniel

Reputation: 13122

This doesn't directly address your issue, but... why not avoid copy and paste?

If you are only pasting the values, then you don't need to copy. When you do step 1. "Go to another spreadsheet and copy the content" instead of copying, you can store the values into an array. When you want to "paste" the values, just place the array back into the ActiveSheet.

Now that I've said that, here's example code:

Private myArray
Sub fakecopy()
    myArray = Selection
End Sub
Sub FakePaste()
    With ActiveSheet
        If IsArray(myArray) Then
            .Range(.Cells(1, 1), .Cells(UBound(myArray), UBound(myArray, 2))) = myArray
        Else
            .Cells(1, 1) = myArray
        End If
    End With
End Sub

When you want to paste the values, just set the appropriate range to equal the array.

Additionally, if when you protect the sheet you set UserInterfaceOnly = true, then there is no need to unprotect it to run your code.

Upvotes: 1

MisterWeary
MisterWeary

Reputation: 605

Okay, I've solved it (but someone may have a better solution). I believe the problem was that when I used the DataObject method of keeping the clipboard contents it stripped the formatting and so it couldn't PasteSpecial, that's actually fine with me because I'm only after the data anyway! With that in mind, this seems to work:

Dim dClipBoard As MsForms.DataObject
Dim sClipBoard As String
Set dClipBoard = New MsForms.DataObject
dClipBoard.GetFromClipboard
sClipBoard = dClipBoard.GetText
ActiveWorkbook.ActiveSheet.Unprotect
Set dClipBoard = New MsForms.DataObject
dClipBoard.SetText sClipBoard
dClipBoard.PutInClipboard
Range("A1").Select
ActiveSheet.Paste

Upvotes: 4

Related Questions