Clearing Clipboard in Excel

[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.

[wiz126]

If you are talking about VB6 then

Clipboard.Clear

else tell mods to move it

[Static]

that wont work in VBA there is no "Clipboard" object

set the CutCopyMode = False

Application.CutCopyMode = False

[RobDog888]

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.

[RobDog888]

You could just clear the clipboard from outside of Excel if all else fails.

Are you saving the workbook before you close it?

[RobDog888]

Ok, I played (tested) around a bit and came up with this in case you are saving your changes before closing the workbook.

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.

's Geoff

[Esham]

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.

[RobDog888]

Hmm, try adding the code to the BeforeClose event.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CutCopyMode = False
End Sub

[c03cg]

Would turning off warning not do it? or would that keep the data on the clipboard?

[RobDog888]

I tried turning off the alerts earlier but it didnt affect the clipboard notice.

[Greg Maloney]

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.

[Esham]

Application.DisplayAllerts does not work. Neither does the OnError because it is not a triggered error.

[RobDog888]

Didnt you try the code in my previous post (#6)? It works.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CutCopyMode = False
End Sub

[scottiebhoy]

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

[Liquid Metal]

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.

Range(“A1”).copy
Application.CutCopyMode = False

[Siddharth Rout]

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...