copy to Clipboard vba problems

[utuber]

Hello all,
I have a problem when I'm using a macro to copy a cell contents then I use ctrl+v to paste to a webform or to another cell in excel. The macro functions perfectly until I put the computer into either "sleep" or "Hibernate". Upon waking I find ctrl+v pastes nothing into the webform, nothing into NotePad and pasting back into excel the result is 2x question marks in a square.


Another thing I found was that if I open the Clipboard I see the values arriving there and if I left click the value in the Clipboard list I can then paste to excel, notepad and webform as many times as I like until I use the macro again to copy another cell value then I'm back to the problem pasting as explained above.


I have found a solution which is to change the Region and re-log back in. This of course is not suitable and defeats the sleep/hibernate advantage.


I'm running Win10 x64 and Excel 2013


Here is the macro I am using.
----------------------------------
Sub CopyToClipboard()
'Macro to copy cell value to clipboard to be pasted elewhere

Dim clipboard As MSForms.DataObject
Dim strSample As String


Set clipboard = New MSForms.DataObject

strSample = ActiveCell.Value 'Copies Active Cell Value to insert in Clipboard

clipboard.SetText strSample
clipboard.PutInClipboard

End Sub
----------------------------------
Any help with this problem would be very much appreciated.
Thanks for reading
Peter

 

[utuber]

Hello again. I have run the spreadsheet on another computer with Win7 and the problem does not exist. Seems only a problem on Win10 OS...

I'm confused..

 

[shg]

I recollect it's a bug in W10, and the workaround requires API as an alternative. There's an example by RoryA here in the forum someplace, but the one I can put my finger on is at https://chandoo.org/forum/threads/clipboard-copy-vba-code-not-working-in-windows-10.37126/

I don't use W10, so can't say that the code works - but surely Rory's does if you track it down.

 

[utuber]

Thank you so much shg. I was sure someone much more skilled than myself would come up with a solution to my latest Excel VBA problem.

I didn't find RoryA posting but Chandoo.org's fix using Windows API seems to be working well.

Thanks once again for your help

Peter