Why does Excel vba copy to clipboard inconsistently?

Question

I have an excel macro that does two very simple things:

1. It displays the current date and time in a little window.
2. It copies the display as a text string for pasting into other apps as needed.

The cell that is displayed has the following formula in it:

=TEXT(NOW(),"yyyy.MM.dd hh:mm:ss")

Every 5 seconds, the macro refreshes the time and the clock ticks.

My problem is that when I copy the time from the cell, I don't consistently get the contents pasted to the clipboard. Sometimes the cell contents are posted to the clipboard. I can't figure out why it works sometimes and not others as there isn't a lot going on. It should just always work.

I know the data aren't on the clipboard because I can try pasting the clipboard into different programs like notepad and other text apps and nothing happens.

The entire code is in a single module.

     Dim stopSwitch As Integer
     Dim NextTick
     Sub myupdate()
        If ActiveCell.Address = "$B$1" Then
            growWindow ' resize window beyond just clock display
            stopTime '
            Exit Sub ' stop updating
        End If

        Range("a1").Select
        Calculate

        DoEvents
        If ActiveWorkbook.Name = "calendar clock.xlsb" Then shrinkWindow
        NextTick = Now + TimeValue("00:00:05") ' give me 5 seconds to copy/paste
        Application.OnTime NextTick, "myupdate"
        ThisWorkbook.Save ' futile attempt to prevent save dialog
    End Sub

    Sub auto_open()
    ' to stop clock, tap right arrow to select cell b1 when workbook is active
     Range("a1").Select
     myupdate

    End Sub

    Sub growWindow()
        Application.Width = 768
        Application.Height = 621.75
      ThisWorkbook.Save
    End Sub

    Sub shrinkWindow()
      ' strip decorations so window is as small as possible
      Application.DisplayFormulaBar = False
      ActiveWindow.DisplayGridlines = False
      ActiveWindow.DisplayHeadings = False

      ' move window to second monitor and size to single cell display
      Application.WindowState = xlNormal
      Application.Top = 0
      Application.Left = -720
      Application.Width = 174
      Application.Height = 127
      ActiveWindow.WindowState = xlMaximized
    End Sub

    Sub stopTime() ' called when workbook is closed
        On Error Resume Next
        Application.OnTime NextTick, "myupdate", schedule:=False
        Range("b1").Select
    End Sub

    Sub copyTime()
      Range("a1").Copy ' copy time
      Range("f5").PasteSpecial xlPasteValues ' strip formatting
      Range("f5").Copy ' copy time as text
      DoEvents ' hack to attempt to make copy work consistently
    End Sub

The above code sizes the window and updates the clock every 5 seconds.

To copy the clock as text to the clipboard, I have the following code in the workbook

Private Sub Workbook_Activate()
   Application.OnKey "^c", "module1.copyTime"
End Sub

Private Sub Workbook_Deactivate()
   Application.OnKey "^c"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   ' turn off auto update
   Module1.stopTime

   ' resize window so if I open another spreadsheet, it's a reasonable size
   Application.WindowState = xlNormal
   Application.Width = 768
   Application.Height = 621.75
   Application.OnKey "^c" 

   ThisWorkbook.Save ' try to prevent save dialog at close
End Sub

I modified the copyTime function to verify the ^C is seen by selecting the unformatted cell and I can see that the data consistently go to the cell so I know my problem isn't with the Range("a1").copy step in copytime or the pastespecial to cell f5.

That leaves the range("a5").copy command as the bad actor when the copy fails which is weird. It's as if copy works as long as the data are kept inside the spreadsheet but fails to update the external clipboard consistently.

That observation led me to try setting application.cutcopymode to xlcopy, true and false to see if that helped. The only effect I saw from trying all the settings is whether I saw f5 get highlighted with a marquee or not - none of the setting forced a copy to the external clipboard.

I tried waiting for a clock tick before copying to see if something was clearing the clipboard following the copy if it was time to update the clock. That appeared to help somewhat but, again not consistently.

So why does the copy fail to always update the clipboard? And why does it not work when it doesn't and does when it does? Even better, how can I modify this code so it always exports to the external clipboard?

Answer 1

Try using this method, it's always reliable for me

Dim TimeInClip As MSForms.DataObject
Set TimeInClip = New MSForms.DataObject
TimeInClip.SetText Range("A1").Value
TimeInClip.PutInClipboard

Answer 2

Try

Sub copyTime()
  Range("a1").Copy ' copy time
  Range("f5").PasteSpecial xlPasteValues ' strip formatting
  Application.CutCopyMode = False ' Clear Excel clipboard
  Range("f5").Copy ' copy time as text
  DoEvents ' hack to attempt to make copy work consistently
End Sub

You said that you tried Application.CutCopyMode, but have you tried it that way?
It only forces the application to clear the clipboard before copying something else, which should then copy properly on the fresh clipboard.