PDA

View Full Version : [SOLVED] "CutCopyMode" . . . What Is It?



Cyberdude
08-31-2005, 09:00 PM
I've been trying to discover the nature of "CutCopyMode", and so far have had no success. If I'm in "Cut Mode" or "Copy Mode", just what does that mean? Why would I want to manually turn it off or on? :bug:

Ken Puls
08-31-2005, 09:08 PM
Hi Sid!

Put someting in cell A1 and try this:


Sub Test()
Range("A1").Copy
Range("B1").Pastespecial Paste:=xlvalues
End Sub

Notice how the border around cell A1 is still flashing? Go to any other cell on the worksheet and hit Ctrl + V (or however you like to paste)


Now try this
Sub Test2()
Range("A1").Copy
Range("B1").Pastespecial Paste:=xlvalues
Application.Cutcopymode = False
End Sub

No flashing... no pasting.

Basically CutCopyMode is the setting that tells you if there is something ready to paste. You'd normally turn it off from the main Excel interface by hitting escape to get rid of the flashing around the selection.

Hope this helps,

BlueCactus
08-31-2005, 09:29 PM
Hope this helps,
What would help would be for MS to just get rid of it. :devil: Cut, Clear, Copy, Paste always worked just fine until Excel came along...... or am I in the minority here? :giggle

ALe
09-01-2005, 12:51 AM
The only way I use CutCopyMode(=False) is at the end of a procedure just to avoid range flashing on the screen.

Cyberdude
09-01-2005, 10:48 AM
That's very interesting. My next question could very well have been "how do you get rid of the flashing?". I've wondered about that. Thanx, Guys. :bow:

johnske
09-01-2005, 03:24 PM
Hi Cyber,

Another reason to put Application.Cutcopymode = False is to clear the clipboard.

If you (say) have a loop where you have to copy a number of things, put Application.Cutcopymode = False after each paste or you will soon be getting messages about being unable to comply because the clipboard is full.

HTH,
John

Cyberdude
09-02-2005, 02:45 PM
Hey, John, another interesting reply. Regarding the clipboard-clearing benefit, if I am doing a copy/paste in a loop, is there any difference between setting CutCopyMode to FALSE after EACH paste execution or setting it FALSE after the loop finishes? I guess I still don't quite understand it. If I have a loop that sets CutCopyMode to FALSE in each iteration, what turns it back on? Does the execution of a COPY set CutCopyMode to TRUE? Now that I think about it, of course that's what happens. So whether you set it to FALSE internal to the loop would pretty much depend on how much is being copied per iteration. I would guess that turning it off at the end of the procedure would be adequate for most applications.

Ken Puls
09-02-2005, 02:49 PM
Hey, John, another interesting reply. Regarding the clipboard-clearing benefit, if I am doing a copy/paste in a loop, is there any difference between setting CutCopyMode to FALSE after EACH paste execution or setting it FALSE after the loop finishes? I guess I still don't quite understand it. If I have a loop that sets CutCopyMode to FALSE in each iteration, what turns it back on? Does the execution of a COPY set CutCopyMode to TRUE? Now that I think about it, of course that's what happens. So whether you set it to FALSE internal to the loop would pretty much depend on how much is being copied per iteration. I would guess that turning it off at the end of the procedure would be adequate for most applications.

:rotlaugh: Nice work, Sid! :thumb

johnske
09-02-2005, 02:56 PM
.... I would guess that turning it off at the end of the procedure would be adequate for most applications.On the contrary, that would be adequate for a few applications and there is generally no way of knowing in advance whether the current use is one of them.

In fact, to be more correct, as you don't know whether there is anything on the clipboard prior to running your macro, it's probably best to also set it to False before entering the loop as well i.e.

Application.CutCopyMode = False
For PasteIt= 1 to 100
(Do your paste)
Application.CutCopyMode = False
Next PasteIt

Zack Barresse
09-02-2005, 02:59 PM
Sid, you should just be able to set it to False when you are done with your procedure. Adding to the clipboard will just tack it on, just as you would extend a list. What you come up against is the resources you have to allocate for the clipboard.

If you are copying huge amounts of data, it may be worthwhile - performance-wise - for you to set the CutCopyMode to False after every iteration. Otherwise, it's pretty much a waste of space. I guess the question comes down to, "How much of a clean freak are you?" (Not to imply that it's bad, but can be over-done.)

And btw, you don't set CutCopyMode to True, you set it to xlCopy or xlCut. Paste is used by another method altogether. There is a better [explaining] diagram in the Object Browser (Alt + F11 | F2).

johnske
09-02-2005, 03:28 PM
....If you are copying huge amounts of data, it may be worthwhile - performance-wise - for you to set the CutCopyMode to False after every iteration. Otherwise, it's pretty much a waste of space. I guess the question comes down to, "How much of a clean freak are you?" (Not to imply that it's bad, but can be over-done.)... Hi Zack,

According to my Help files (2000) I can only fit 12 separate items on the clipboard before getting an error message, so it's not just about the amount of data, it's also about the number of iterations/items.

Regards,
John

Zack Barresse
09-02-2005, 03:44 PM
I can fit 24 in 2003 ..

TonyJollans
09-02-2005, 04:12 PM
There is some confusion here :)

Setting CutCopyMode = False clears the marching ants and the Windows Clipboard of whatever the ants were marching round. It does not clear anything else from the Windows clipboard and it does not clear the Office Clipboard.

The Windows Clipboard contains, at most, one item, the last thing copied regardless of the app. Copying something else overwrites it.

The Office Clipboard contains up to 24 (in 2003) different copied items with the oldest item being discarded to add a new item when it is full. It can be manipulated through the Excel GUI (and partially in code, though not in any obvious manner).

Zack Barresse
09-02-2005, 04:19 PM
True. Looking back, I think we should have said Excel's clipboard, not windows clipboard. Even being so, that is not true (what we said) because it does not entirely clear it. (Tony is the master! :bow: ) To clear the windows OS clipboard, you can use API, like THIS (http://vbaexpress.com/kb/getarticle.php?kb_id=205). Setting the CutCopyMode = False will not allow you to paste anymore, is that a more technically correct term?

TonyJollans
09-02-2005, 04:43 PM
My understanding, which could well be wrong , is ...

If there is a region surrounded by marching ants then that region has been copied to, and is currently on the Windows Clipboard and has been copied to, and is currently number one on the Office Clipboard.

If and only if there is a region surrounded by marching ants will setting CutCopyMode = False have any effect at all. That effect will be to clear the marching ants and to clear the Windows clipboard.

The API method (presumably as per the KB you reference) will clear the Windows Clipboard of whatever is on it, whatever its source.

The Office Clipboard is a whole different beast - and I lied a little when I said you could get at it in code. I just had a little play, and you can work with the Clipboard CommandBar in 2000, but since it moved to the Task Pane, I don't know how to do it - I'm still looking, I don't know if it's possible or not.

johnske
09-02-2005, 07:01 PM
There is some confusion here :)

Setting CutCopyMode = False clears the marching ants and the Windows Clipboard of whatever the ants were marching round. It does not clear anything else from the Windows clipboard and it does not clear the Office Clipboard.

The Windows Clipboard contains, at most, one item, the last thing copied regardless of the app. Copying something else overwrites it.

The Office Clipboard contains up to 24 (in 2003) different copied items with the oldest item being discarded to add a new item when it is full. It can be manipulated through the Excel GUI (and partially in code, though not in any obvious manner).Hi Tony,

I haven't tested this extensively, I'm only going on what I've read in the help files and elsewhere on the web.

If you read your Help files re the clipboard you'll see that you have a certain number of copies that can be made (varies from version to version of course) and you will also find instructions in the Help file for clearing the Office clipboard, now, record that action.

i.e. specifically, Open excel, copy something (anything) on the s/s, now...

Go to View > Toolbars > Clipboard (note that this is the Office clipboard)
Go to Tools > Macro > Record New Macro... > OK
Click 'Clear Clipboard' on the clipboard toolbar

Now end your macro recording and open the VBE window to find the macro you've just recorded and note the single line of code in it which resulted from clicking 'Clear Clipboard' is...
Application.CutCopyMode = False

Regards,
John :)

TonyJollans
09-03-2005, 12:58 AM
If there are marching ants then pressing "Clear Clipboard" will, as well as clearing the office clipboard, also clear the marching ants and the windows clipboard. If there are no marching ants and you press "Clear Clipboard", then nothing will be recorded. This is because "CutCopyMode = False" is an extra action performed by "Clear Clipboard" under certain circumstances, and it is recorded when it is triggered.

Running "CutCopyMode = False" will not clear the Office Clipboard, nor will it even clear the Windows Clipboard unless there are marching ants.

johnske
09-03-2005, 03:37 AM
Hi Tony,

I know I get no error messages when I insert Application.CutCopyMode = False in a loop in some procedures that bomb out when it's not inserted. You're forcing me to experiment a bit more now :rofl: ... This is strange

If you copy something and then run "Application.CutCopyMode = False" the clipboard is certainly shown as still having something on it. However when you try to paste, nothing is pasted, then, when you close and re-open the workbook you will then find the clipboard is now shown as being empty.


Here's another experiment, select and copy the sheet and then exit the workbook. You should see a message similar to the one shown below.

Now repeat the above but run "Application.CutCopyMode = False" before exiting the workbook. There is now no message shown, this would also seem to indicate that the clipboard is indeed empty.

Regards,
John :)

TonyJollans
09-03-2005, 05:26 AM
Hi John,

You are, I think, confusing the Windows and Office Clipboards. You only get indication in Excel of what is on the Windows clipboard in a limited set of circumstances (when a range is dotted, or maybe when you quit as you show). You get indication of what is on the Office clipboard via the Clipboard toolbar - what is on the Windows clipboard may be (but also may not be) one of the items on the Office clipboard.

Rather than post a full explanation here, I've started writing this up as an article which I should submit later today.

TonyJollans
09-06-2005, 03:24 AM
I have written up what I know about the Office Clipboard and submitted it as an Article - somebody will approve it (or not) in due course. As always when I start writing I have rambled on and probably missed some vital piece of information so please put me right when you've read it.

Cyberdude
09-06-2005, 10:09 AM
Thanx for the notice, Tony. I thought I was the only one who is confused on this topic, but clearly a lot of people are.
I submitted the same question to MrExcel forum, and got one very poor reply. I did a search in the Microsoft KB, and found nothing. I look forward to seeing your article (if it ever gets approved). http://vbaexpress.com/forum/images/smilies/039.gif

Ken Puls
09-06-2005, 10:10 AM
I look forward to seeing your article (if it ever gets approved). http://vbaexpress.com/forum/images/smilies/039.gif

It will, Sid. No worries there, it just takes time. :yes

johnske
09-18-2005, 02:17 AM
Thanx for the notice, Tony. I thought I was the only one who is confused on this topic, but clearly a lot of people are.
I submitted the same question to MrExcel forum, and got one very poor reply. I did a search in the Microsoft KB, and found nothing. I look forward to seeing your article (if it ever gets approved). http://vbaexpress.com/forum/images/smilies/039.gifHi cyber,

just to let you know that Tonys very informative and comprehensive article about the office and windows clipboard has now been published and can be found here http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=50 :hi:

Regards,
John :thumb