Clearing Clipboard in Excel
In VBA I am copying large amounts of info from one workbook to another. When I go to close the workbook it prompts me if I want to retain the clipboard info. How do I get rid of this message or clear the clipboard. Im not sure what forum to put this in, Im sorry.
Re: Clearing Clipboard in Excel
If you are talking about VB6 then
else tell mods to move it
Re: Clearing Clipboard in Excel
that wont work in VBA there is no "Clipboard" object
set the CutCopyMode = False
Application.CutCopyMode = False ;)
Re: Clearing Clipboard in Excel
CutCopyMode only cancels or returns the mode that the active cell is in. It can either remove the copy focus rectangle or return the state that the copy/cut is in. It doesnt clear the clipboard.
Moved from Classic VB forum. :)
Re: Clearing Clipboard in Excel
You could just clear the clipboard from outside of Excel if all else fails.
Are you saving the workbook before you close it?
Re: Clearing Clipboard in Excel
Ok, I played (tested) around a bit and came up with this in case you are saving your changes before closing the workbook.
VB Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.CutCopyMode = False
End Sub
I should have tested before posting. The definition didnt say anything about clearing the clipboard. :blush:
:thumb:'s Geoff
Re: Clearing Clipboard in Excel
No, I am not saving the workbooks. I'm not sure what you mean by clearing the clipboard outside of excel. Excel is the only application I am working in. I've tested the copyclip mode also and that only acts the way RobDog888 explained it. Looks like I'll just have to sit there and hit "No" everytime the dialoge box appears.
Re: Clearing Clipboard in Excel
Hmm, try adding the code to the BeforeClose event.
VB Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CutCopyMode = False
End Sub
Re: Clearing Clipboard in Excel
Would turning off warning not do it? or would that keep the data on the clipboard?
Re: Clearing Clipboard in Excel
I tried turning off the alerts earlier but it didnt affect the clipboard notice.
Re: Clearing Clipboard in Excel
Have you tried the : 'Application.DisplayAlerts = False' command ? Or is that what you are referring to in your last comment ?
You could also try the : 'On Error Resume Next' command before the problem but you must ensure that you follow this up with the 'On Error Goto 0' command directly after the line that's causing the problem, to ensure that you don't ignore further errors.
Re: Clearing Clipboard in Excel
Application.DisplayAllerts does not work. Neither does the OnError because it is not a triggered error.
Re: Clearing Clipboard in Excel
Didnt you try the code in my previous post (#6)? It works.
VB Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CutCopyMode = False
End Sub
Re: Clearing Clipboard in Excel
I was experiencing the same problem, copying from multiple workbooks into a master dump, having to hit 'no' each time the prompt appeared asking if i wanted to retain the clipboard info.
By simply inserting the 'Application.CutCopyMode = False' code immediately before closing each workbook, the prompt was eliminated
Thanks Static
Re: Clearing Clipboard in Excel
The best way to clear the clipboard of huge amount of data is to copy ONE empty cell and it will take care of itself.
Code:
Range(“A1”).copy
Application.CutCopyMode = False
Re: Clearing Clipboard in Excel
Quote:
Originally Posted by Esham
In VBA I am copying large amounts of info from one workbook to another. When I go to close the workbook it prompts me if I want to retain the clipboard info. How do I get rid of this message or clear the clipboard. Im not sure what forum to put this in, Im sorry.
A better suggestion... ;) Use APIs
Check this out...
http://vbforums.com/showthread.php?t=449922
Hope this helps...