Clip board

elgringo56

Well-known Member
Joined
Apr 15, 2002
Messages
869
Here is a real dumb one, probably. Is there a way I can clear the clip board from within a macro?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
try

Sub ClearClipboard()
Application.CopyObjectsWithCells = False
Application.CutCopyMode = False
End Sub
 
Upvote 0
Further to my problem, the code previous does work to clear the clipboard, but only when there is oneitem in it. When there are two or more items in the clipboard it wont work no matter how many times you run it.
Im using exl 200 with the clipboard toolbar showing.
Please can anyone help???
 
Upvote 0
On 2002-05-27 09:19, vac wrote:
Further to my problem, the code previous does work to clear the clipboard, but only when there is oneitem in it. When there are two or more items in the clipboard it wont work no matter how many times you run it.
Im using exl 200 with the clipboard toolbar showing.
Please can anyone help???

<pre/>
Sub ClearClipboardEl2000()
On Error Resume Next 'incase clipboard IS empty
Application.CommandBars("Clipboard").FindControl(ID:=3634).Execute
End Sub
</pre>
 
Upvote 0
Ivan,
I found this code for clearing the clipboard in XL2000, but it doesn't seem to work in Xl2002. I tried cycling through the controls on the Clipboard commandbar, but none were found?? I found that the clipboard manager is called "Task Pane", but it only has a single text box control. I tried cycling through commandbarbuttons, controls, and objects for Clipboard, and didn't find any? Can you let me know how you identified the ID?

Application.CommandBars("Clipboard").FindControl(ID:=3634).Execute

Thanks
 
Upvote 0
I also get an error with that FindControl method using 2003.


elgringo56 - -

The clipbopard is a Windows object, not exclusively an Excel object. Excel supports a couple dozen data formats (BIFF4, BIFF5, etc) that might be present on the clipboard which must be addresed for the clipboard to truly be cleared.

Someone / anyone correct me if I am wrong or if your version of Excel fails with the following ClearClipboard macro, but it seems to work for me in all cases (drawing objects, charts, audio files...whatever is on the clipboard).


In a standard module:

Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long

Sub ClearClipboard()
OpenClipboard (0&)
EmptyClipboard
CloseClipboard
End Sub
 
Upvote 0
Thanks Tom,
Maybe you can help me with an associated problem, why it is that I'm looking for this. I have a process that has 1 main worksheet with a filter. I then filter the data, based on another list, and copy the data to a new workbook. It will add worksheets to the new workbook until another variable changes, then it will save and close the new workbook, then start the cycle again.
The process slows down measureably by the 10 th worksheet, but then clears up when the workbook is saved and a new one is open. Watching from the task manager, you can see that memory usage going up in steps each time a worksheet is added. Then when the workbook is saved and closed, the memory drops like a ski slope. I believe that the macro running is preventing some type of clean up of memory from occuring or that the close is triggering a clean up at that point. The only problem is, is that when the excel clipboard manager is open, this doesn't seem to happen?? I don't understand why that would occur, but I thought the memory issue might be a build up in the clipboard, but that doesn't seem to be the case. It seems like when the excel clipboard manager is open, that excel deals with clipboard objects differently then when it's closed? I can run with the clipboard object open, but I want to determine how to release the used memory in code, if it's possible.
 
Upvote 0
Tom Urtis said:
...Someone / anyone correct me if I am wrong or if your version of Excel fails with the following ClearClipboard macro, but it seems to work for me in all cases (drawing objects, charts, audio files...whatever is on the clipboard)...

Yup, works for me too. I use this quite often as I use my clipboard tremendously. I haven't found anything it doesn't work on yet.
 
Upvote 0
Cbrine - - If you post your code it would be easier to identify areas where your memory is being used. My guess (only a guess because I have not sen your code) is that maybe you are setting variables for ranges or worksheets but not setting them back to Nothing, or there is excessive selecting or activating, or you are declaring variables as Variant when other less-burdensome variable types would suffice, or you are temporarily copying & saving more data on the clipboard (formulas or formatting) than necessary, or who knows what else. Post your macro and someone may be able to point out areas where an improvement is possible. It would also help if you give whatever detailed text description you can along with the code, so people can see why you are doing what you are doing and maybe suggest alternative approaches.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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