title: Why does Excel vba copy to clipboard inconsistently?
body: <p>I have an excel macro that does two very simple things:</p>
  
  <ol>
  <li>It displays the current date and time in a little window.</li>
  <li>It copies the display as a text string for pasting into other apps as needed.</li>
  </ol>
  
  <p>The cell that is displayed has the following formula in it:</p>
  
  <pre><code>=TEXT(NOW(),"yyyy.MM.dd hh:mm:ss")
  </code></pre>
  
  <p>Every 5 seconds, the macro refreshes the time and the clock ticks.</p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>The entire code is in a single module.</p>
  
  <pre><code>     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
  </code></pre>
  
  <p>The above code sizes the window and updates the clock every 5 seconds.</p>
  
  <p>To copy the clock as text to the clipboard, I have the following code in the workbook</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>So why does the copy fail to <em>always</em> 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?</p>