title: Excel-VBA: Adding a button clears the clipboard - workaround needed
body: <p>OK, so I have some worksheet code that detects when a cell in a range is selected and creates a button alongside it. Everything appeared to be OK, but something about creating the button clears the clipboard. Here's the original code that is called whenever a cell in the relevant range is selected:</p>
  
  <pre><code>Public Sub splitTransactionIn(ByVal Target As Range)
    Dim btn As Button
    Set t = ActiveSheet.Range("E" &amp; Target.Row)
    rowNumber = Target.Row
    On Error Resume Next
    ActiveSheet.Buttons("Split").Delete
    Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
    With btn
      .OnAction = "btnIn"
      .Caption = "Split transaction"
      .Name = "Split"
    End With
  End Sub
  </code></pre>
  
  <p>I tried commenting sections out and inserting breakpoints, and as soon as either .OnAction, .Caption or .Name are reached, the clipboard is cleared. Why, I have no idea.</p>
  
  <p>Thinking about it logically, I added in a few extra lines of code to capture the clipboard before it was cleared and replace it at the end of the sub, so I end up with this:</p>
  
  <pre><code>Public Sub splitTransactionIn(ByVal Target As Range)
    Dim btn As Button
    Dim objData As New MSForms.DataObject
    Dim strText
  
    objData.GetFromClipboard
    strText = objData.GetText()
    Set t = ActiveSheet.Range("E" &amp; Target.Row)
    rowNumber = Target.Row    ' gets used elsewhere...
    On Error Resume Next
    ActiveSheet.Buttons("Split").Delete
    Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
    With btn
      .OnAction = "btnIn"
      .Caption = "Split transaction"
      .Name = "Split"
    End With
    objData.SetText strText
    objData.PutInClipboard
  End Sub
  </code></pre>
  
  <p>Now, this works, but only to a point. So I select a cell in the range and copy it (Ctrl-C), I now select (say) 3 cells further down in the same range and try to paste (Ctrl-V). What I get is an error message: "Data On Clipboard Is Not The Same Size And Shape As Selected Area" and if I hit OK, it just pastes to a single cell. I know the source and targets are different sizes, but elsewhere in the sheet this works fine when copy/pasting. Clearly I have done something odd in reading/writing the clipboard, but I have no idea what.</p>