Working with options from within Tools Options clears the Clipboar

[Guest]

Good afternoon,

Can anyone tell me why when, if I copy something to the Clipboard, then try
to change a setting to something like scroll bars, worksheet tabs, formula
bar etc, the clipboard empties?

This works in "normal" Excel, as well as VBA - do a copy, then select
"Tools" and "Options" from the menu, and the carousel line around the
selection disappears!

It's causing me MAJOR problems - I'm using 2003 on XP

Thanks

Pete

 

[Tom Ogilvy]

In code and manually, it is best to do the copy and paste as one set of
actions with no intermediate actions.

As to why, I would imagine that Excel errs on the side of caution in
assuming that almost any action outside of pasting could change the
environment of what was copied.

 

[Guest]

Tom,

I agree, except that I'm not doing a straight pasts, trather I'm having to
do two paste specials, one for values and one for formats, so it's not that
easy.

Thanks for your thoughts

Pete

 

[Tom Ogilvy]

If you do them with no intermediate steps, then I am not sure how that
differentiates your situation from what I advised. I have never had any
trouble doing that (following my advice).

 

[Guest]

Tom,

Following your advice, I'm going to see if I can do a copy and paste special
in one go, as I've never done it before.
However, your other post about enableevents=false solved this problem
anyway, as since I'm now no longer running the window view changes. I don't
lose the contents of the clipboard!"

Once again, thanks for your help

Pete

 

[Tom Ogilvy]

One go doesn't mean one command. Paste Special requires two commands
(separate copy and paste), but they should be contiguous in your code.

 

[Guest]

Tom,

I thought that was the case, but didn't want to say so until I'd proved
otherwise.
Anyway, the problem's now been solved, thanks to your EnableEvents tip.
Off to Glastonbury for a well earned rest for a couple of days now, leaving
behind a fully working code-compromise-free solution (bet it will have
crashed when I get back, though! )

Have a good weekend

Pete