1

Background

I have an Excel 2007 form where the user enters some data into a textbox and if they click the OK command button, a macro runs based on that text. However, in the case that they copy a string from somewhere else (eg Ctrl + C) and then paste for the first time and press OK, the copy buffer seems to be cleared; if they try pasting again anywhere a second time, nothing happens. However, if they click the Cancel command button, they retain their second+ paste ability. The macro that is run upon the OK click is pretty simple: it creates a new sheet, inserts some data from a database, and performs some loop checks on it, so I don't think any of the VBA methods would clear the copy buffer. The only thing I can think of is the .Select it uses at one point to then setup a .PrintArea, but that shouldn't have any copy/paste effect as it doesn't copy/paste selected data as all of that is done through Cells(row, col) = Cells(row, col) syntax. Furthermore, when I have the clipboard pane up, it doesn't wipe anything; the entries remain there.

Question

Is it simply a bug that Excel 2007 eats the copy buffer after executing a macro linked to a command button/textbox or is there something that is likely causing the issue? In the case that it's a bug, is there a method to put back into Ctrl + C the string that the user originally entered?

Community
  • 1
  • 1
Kurt Wagner
  • 3,295
  • 13
  • 44
  • 71
  • 1
    What do you mean "the Ctrl + C selection vanishes"? Does the copy/paste buffer get cleared out so you can't paste it a 2nd time? – PowerUser May 30 '13 at 19:24
  • The copy/paste buffer getting cleared out sounds like an apt description as I can't paste twice, yes. If that's the case then the question is does running a macro naturally do that or are there methods that do that in VBA even if they don't copy/paste... – Kurt Wagner May 30 '13 at 20:03
  • You seem to have looked the program over thoroughly, so I'd take a step back from it. Do the users have any special security software that intentionally clears the copy buffer (either after a time limit or after pasting the data once)? Have you been able to *consistently* replicate this issue yourself? – PowerUser May 30 '13 at 22:17
  • Nothing overly complex. I was able to consistently replicate the issue on multiple macros. I created a very simple form + textbox macro linked to an image and I was able to copy just fine. So it looks like the issue is something method wise is clearly the copy buffer, but as to what it is given the multitude of methods within the original macro is beyond me. Gonna experiment with breakpoints and see if I can still copy after certain breakpoints. – Kurt Wagner May 31 '13 at 16:55

1 Answers1

0

I found the issue. I didn't look into every single sub the macro called because there are a lot of them, most of which appear trivial. However one of the seemingly trivial ones had a Selection.Copy which is where if I set a breakpoint the copy buffer gets cleared and set to the selection.

While I was unable to change this sub to not use selection due to the complexity of the selection process, I was able to append a sub at the end of the macro that takes in the original macro input and does the following:

Sub InputToCopyBuffer (someInputVar As String)
    Dim clipboard As MSForms.DataObject
    Set clipboard = New MSForms.DataObject
    clipboard.SetText someInputVar
    clipboard.PutInClipboard
End Sub

Code is modified slightly and taken from How to copy to clipboard using Access/VBA?

Community
  • 1
  • 1
Kurt Wagner
  • 3,295
  • 13
  • 44
  • 71