Protect clipboard data from other programs trying to use clipboard at same time?

C

chuckchuckit

Well-known Member
Joined
Sep 18, 2010
Messages
541
Hello. This is my first post here, so here goes.

I am running an automated macro spreadsheet that every minute or so copies data using the clipboard and pastes it. If before it gets pasted, I use the clipboard to copy something in an other spreadsheet, or other software copies something using the clipboard at the same time, the clipboard contents can get corrupted.

Is there any way to protect the clipboard contents from other programs using the same clipboard area at the same time?

I am assuming there is only one clipboard per computer? What about isolating a program and its clipboard use to a separate core perhaps? (Maybe I am dreaming there...).

I know there are many ways to copy and paste without using the clipboard, but that would make rewritting "a lot" of code. Clipboard protection for each Excel program running would be easier. If there is such a thing.

Thanks. - Chuck
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Is there any way to protect the clipboard contents from other programs using the same clipboard area at the same time?
Click to expand...

Not really but you can find out if the data that is about to be pasted is coming from excel or another running program and act accordingly.

For example, say the Macro CopyRoutine is copying the activecell and then the Macro PasteRoutine is pasting the data onto the cell below. You can use the GetClipboardOwner API as follows to make sure that should any data from outside excel be in the clipboard at the time of pasting it doesn't get pasted.

Code: 
Private Declare Function GetClipboardOwner Lib "user32" () As Long
Private lExcelClipboardOwnerHandle As Long

Sub CopyRoutine()
 
    ActiveCell.Copy
    
    'store the excel current session clipboard handle.
    lExcelClipboardOwnerHandle = GetClipboardOwner

End Sub
 
Sub PasteRoutine()

    If GetClipboardOwner = lExcelClipboardOwnerHandle Then
    
        'Clipboard data coming from excel.
        ActiveSheet.Paste ActiveCell.Offset(1)
        
    Else
    
        'Clipboard data coming from another program.
        'recopy the activecell .
        ActiveCell.Copy
        ActiveSheet.Paste ActiveCell.Offset(1)
    
    End If
 
End Sub
 
Upvote 0
Thanks for the info. Your code will be helpful in some cases for me. But my main Excel programs on one computer are many and they query every minute. Then copy a lot of data which may even corrupt clipboard after it recopies again.

I am just wondering if there might be some other way to isolate what is in the clipboard for whatever milliseconds it needs to be preserved before it pastes it? Kind of like disabling events type of thing?

Perhaps some sort of keyboard buffering that uses additional memory when needed?

Otherwise it is starting to look like I will have to rewrite much code that does not use the clipboard at all.
 
Upvote 0
I am just wondering if there might be some other way to isolate what is in the clipboard for whatever milliseconds it needs to be preserved before it pastes it? Kind of like disabling events type of thing?
Click to expand...

This is the most I could do to preserve the data in the clipboard.

Code: 
Option Explicit
 
Private Declare Function OpenClipboard Lib "user32" _
(ByVal hwnd As Long) As Long
 
Private Declare Function CloseClipboard Lib "user32" () As Long
 
Private Property Let LockClipBoard(ByVal Value As Boolean)
 
    If Value Then
        OpenClipboard (0)
    Else
        CloseClipboard
    End If
 
End Property
 
Private Property Let DisableCopyCutAndPaste _
(ByVal Value As Boolean)
 
    EnableControl 21, Not Value 'cut
    EnableControl 19, Not Value 'copy
    EnableControl 22, Not Value 'paste
    EnableControl 755, Not Value 'pastespecial
 
    With Application
 
        If Value Then
            .OnKey "^c", ""
            .OnKey "^v", ""
            .OnKey "+{DEL}", ""
            .OnKey "+{INSERT}", ""
        Else
            .OnKey "^c"
            .OnKey "^v"
            .OnKey "+{DEL}"
            .OnKey "+{INSERT}"
        End If
 
    End With
 
End Property
 
Private Sub EnableControl(Id As Integer, Enabled As Boolean)
 
    Dim CB As CommandBar
    Dim C As CommandBarControl
 
    On Error Resume Next
 
    For Each CB In Application.CommandBars
        Set C = CB.FindControl(Id:=Id, recursive:=True)
        If Not C Is Nothing Then C.Enabled = Enabled
    Next
 
End Sub

Usage example :

This Test Macro will copy Cell A1 and and paste its contents after 10 secondes onto Cell A2

During the 10 seconds , the user as well as any other programs will not be able to copy anything to the clipboard thus preserving the data until it is pasted by the macro.

Code: 
Sub Test()
 
    Dim t As Long
 
 
    Application.EnableCancelKey = xlDisabled
 
    On Error GoTo Xit:
 
    Range("a1").Copy
 
    DisableCopyCutAndPaste = True
    LockClipBoard = True
 
    t = Timer
    Do
        DoEvents
    Loop Until Timer - t >= 10
 
    ActiveSheet.Paste Range("a2")
 
Xit:
 
 
    LockClipBoard = False
    DisableCopyCutAndPaste = False
    Application.EnableCancelKey = xlInterrupt
 
End Sub
 
Upvote 0
Thank you very much for your efforts on this. I did not think there might be such a possibility. When time permits I will give this a try. If it works inside my automated systems, it will save me an increadable amount of recoding efforts to copy and paste without using the clipboard.

Thanks again.

Chuck
 
Upvote 0
Jaafar,

Thank you very much for your code work on protecting the clipboard during copying. I am in the process of testing it and coding it into my systems. It seems to work extremely well and will save me a very large amount of recoding.

In fact I passed along your code work to someone else who is having the same clipboard copying problems as me. Here is his link there:

http://www.excelforum.com/excel-programming/746766-running-macro-overrides-manual-commands.html

It turns out that when using the clipboard protection code you wrote, it works well, but I have many programs running at the same time. I usually can get them to run not at the same time, but now when I protect the clipboard, if another automated program needs the clipboard when it is protected then it will crash because of the copy fail.

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
So I am looking for some code to be able to tell me if there are any other workbooks on the computer that have a macro currently running.

<o:p></o:p>
If any other macro’s are found running, I would then use your timer wait code to keep trying every few seconds until no macro’s are running and then proceed. But I can’t seem to locate the code that would tell me if any macros are currently running anywhere in other workbooks.

<o:p></o:p>
Thanks again for your clipboard code. After I finish implementing it, I’ll post a copy of how I adapted it for my system for your review if you like.

Chuck
 
Upvote 0
Hi Chuck.

What do you mean by crash ? Do you get an Error message or something ?

It turns out that when using the clipboard protection code you wrote, it works well, but I have many programs running at the same time....
Click to expand...

By "many programs running at the same time", do you mean other workbooks opened in different excel instances ? If so then you may want to try the following :


1- Amend the code in my previous post as follows : ( In red are the new additions )

Code: 
Option Explicit
 
Private Declare Function OpenClipboard Lib "user32" _
(ByVal hwnd As Long) As Long
 
Private Declare Function CloseClipboard Lib "user32" () As Long
 
[COLOR=red]Private Declare Function SetClipboardViewer Lib "user32" _[/COLOR]
[COLOR=red](ByVal hwnd As Long) As Long[/COLOR]
 
 
Private Property Let LockClipBoard(ByVal Value As Boolean)
 
    If Value Then
        [COLOR=red]SetClipboardViewer 0[/COLOR]
        OpenClipboard (0)
    Else
        CloseClipboard
        [COLOR=red]SetClipboardViewer Application.hwnd[/COLOR]
    End If
 
End Property
 
Private Property Let DisableCopyCutAndPaste _
(ByVal Value As Boolean)
 
    EnableControl 21, Not Value 'cut
    EnableControl 19, Not Value 'copy
    EnableControl 22, Not Value 'paste
    EnableControl 755, Not Value 'pastespecial
 
    With Application
 
        If Value Then
            .OnKey "^c", ""
            .OnKey "^v", ""
            .OnKey "+{DEL}", ""
            .OnKey "+{INSERT}", ""
        Else
            .OnKey "^c"
            .OnKey "^v"
            .OnKey "+{DEL}"
            .OnKey "+{INSERT}"
        End If
 
    End With
 
End Property
 
Private Sub EnableControl(Id As Integer, Enabled As Boolean)
 
    Dim CB As CommandBar
    Dim C As CommandBarControl
 
    On Error Resume Next
 
    For Each CB In Application.CommandBars
        Set C = CB.FindControl(Id:=Id, recursive:=True)
        If Not C Is Nothing Then C.Enabled = Enabled
    Next
 
End Sub
 
Sub Test()
 
    Dim t As Long
 
 
    Application.EnableCancelKey = xlDisabled
 
    On Error GoTo Xit:
 
    Range("a1").Copy
 
    DisableCopyCutAndPaste = True
    LockClipBoard = True
 
    t = Timer
    Do
        DoEvents
    Loop Until Timer - t >= 10
 
    ActiveSheet.Paste Range("a2")
 
Xit:
 
    LockClipBoard = False
    DisableCopyCutAndPaste = False
    Application.EnableCancelKey = xlInterrupt
 
End Sub


2- Now let's suppose there is another Macro ( whose name is for example CopyMacroInForeignProgram ) in another workbook in a different Excel instance that is trying to copy the cell K1 during the 10 seconds that the above Test Macro is running with the Clipboard protected . This is what you should do to make it wait until the 10 seconds have passed and the clipboard becomes available again :

Code: 
Private Declare Function GetClipboardViewer Lib "user32" () As Long
 
Sub CopyMacroInForeignProgram()
 
    Do While GetClipboardViewer = 0
 
        DoEvents
 
    Loop
 
    MsgBox "Clipboard now ready for copying."
 
    Range("K1").Copy
 
End Sub

The line Range("K1").Copy should only execute when the Test Macro in the original workbook is finished with the clipboard thus avoiding code interference.

I hope this works for you.
 
Upvote 0
Hi Jaafar,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Yes. They are in different excel instances. I open a new “Excel Browser” (I think that is the term) for each Excel program I then open and run, if that is what you asked.<o:p></o:p>
<o:p></o:p>
They are numerous automated programs that query a web site every minute and copy the data many places. So when I use the clipboard protection code it protects the first program using it. But if one of the other programs is trying to copy it will come back with some sort of error that said it was not able to copy.<o:p></o:p>
I see that by your #2 you are saying that the 2<SUP>nd</SUP> program should be able to see that the clipboard is being used and it waits until clipboard is freed up before it proceeds.<o:p></o:p>
<o:p></o:p>
I will try it and if it works it is the answer already to the other question I just posted on the forum.
<o:p></o:p>
http://www.excelforum.com/excel-pro...other-workbooks-know-my-macro-is-running.html<o:p></o:p>
<o:p></o:p>
Thanks.
<o:p></o:p>
Chuck
 
Upvote 0
Jaafar,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
My preliminary test shows your code works extremely well. That is some very nice work. It has saved me a lot of recoding.<o:p></o:p>
<o:p></o:p>
The many programs I have running at the same time are similar in that they query the web each minute for data, and then processing lasts maybe 3-10 seconds max where all the clipboard copying happens. I may just protect the clipboard during all of each processing so I do not have to find all the places it is copying. Since it usually is only a few seconds per minute per program, any extra delays in getting the clipboard back likely will not matter for how those programs are used.<o:p></o:p>
<o:p></o:p>
Just in case one of my programs crashes before it sets back the clipboard settings, I will put those clipboard recovery setting additionally as part of another macro to recover the clipboard if need be.<o:p></o:p>
<o:p></o:p>
I’ll try some of those things when I get some time hopefully this next week.<o:p></o:p>
<o:p></o:p>
Really appreciate your work on this.<o:p></o:p>
<o:p></o:p>
Thanks again Jaafar.<o:p></o:p>
<o:p></o:p>
Chuck<o:p></o:p>
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Clipboard Error
Replies
2
Views
484
mskli
M
M
  • Question
Cannot paste into Excel 365
Replies
1
Views
863
MartinGM
M
R
  • Question
Doing copy/paste operations in VBA without using the 'real' windows clipboard?
Replies
3
Views
773
MARK858
MARK858
M
  • Question
Error 1004 - Can't Copy as Picture, but only on certain computer
2
Replies
10
Views
332
rlv01
R
YetAnotherDave
  • Question
Question about pasting specific cell information from clipboard using VBA
Replies
0
Views
528
YetAnotherDave
YetAnotherDave

Forum statistics

Threads
1,215,108
Messages
6,123,131
Members
449,097
Latest member
mlckr

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top