Sleeper: Testers wanted

[mdmackillop]

Anyone willing to test this cut and paste function and provide feedback (and hopefully solutions for any bugs found!)
The purpose of the function is to allow Copy/Cut and paste, dragging, autofill etc. without overwriting the destination format.
Please note, if the macro hangs, the PC may be left with

Applications.EnableEvents = False

in which case the Restore Events button will reset this to true.
MD

[Ken Puls]

Hi Malcolm,

No time to work out code, but if you drag a cell down from row 1 it bombs. Dragging right from column A is okay though.

Line it hangs on is:

Case Is = xlDown
MAR = Selection.Offset(-1, 0).Address

[mdmackillop]

Thanks Ken,
I forgot about that. It depends how your MoveAfterEnter is set.
Malcolm

[mdmackillop]

Revised zip file

[Ken Puls]

Broke it again, Malcolm!

Try selecting a mulicell range, say B3:H9. Drag it up using the + handle to row 1 (as if you wanted to overwrite the formatting. It blows up there.

Also noticed that if you select the entire column with the format painter and drop it on another column that the formats change. If you do a row, it sets it back.:dunno

[mdmackillop]

Re-revised zip file. Keep on breaking!
I'm not too worried about the format painter, as the intention is that this will be used on a fully formatted sheet, but I've tried a quick fix anyway!

[Anne Troy]

Mayhaps Ken has found his niche. Software tester! :)

[mdmackillop]

If he's good enough, he might become an apprentice Approver! (in due course).

[Ken Puls]

If he's good enough, he might become an apprentice Approver!

Okay, that's it! Your app is toast now! Lemme at it! :rotlaugh:

Actually, I kind of enjoy testing. I don't care how good you are, you can never anticipate everything that a user might do to your file. Plus, it's really great to see the approaches used to do things you never considered.

[Anne Troy]

Since we have no "toast" icon, I thought this one was appropriate:


:dau:

[mdmackillop]

Hi DB,
One of my favourite smilies!

Hi Ken,
When I think of the amount of time I have spent restoring formats, I'm surprised I never thought of creating this function myself. Thanks go to Zorvek at EE for the inspiration.

MD

[Anne Troy]

So....where's Zorvek? Get 'em over here!! :)

[roos01]

Interesting thread MD,
But somehow I think I'm missing something here. I tried using your last zip file.
I am using excel 2003. When I use the drag down of a cell into multiple cells then it first changes the format and then goes into your macro. so nothing different happens with the standard functionality of excel. And formats are overwritten.
when there is a formula involved excel would also copie this formula downwards.

anther thing is when draging the selection just one cell below then it wouldn't get into your macro since: If y = z Then Exit Sub

as I said. perhaps Im missing something.

regards,

Jeroen

[mdmackillop]

Hi Jeroen,
Try clicking on the Restore Events button, It sounds like the code is not running at all. I've posted here with some drag/cut and paste examples.
Regards
Malcolm
http://www.geocities.com/mdmackillop/CutPasteFormat2.xls

[Ken Puls]

Hi Malcolm,

Myabe I'm not as close as I'd like to be to that "Apprentice Approver" title after all! LOL! I've been meaning to get back to trying to break this again, but just haven't had the time yet. I will get there though, just give me a couple of days! :yes

[roos01]

Hello MD,
i ran it also in debug mode. therefor I noticed that when only one cell is dragged downwards y becomes equal to z.

also the cell color was first set before it ran into the macro. so the cells became first pink with values when it ran into the macro. (excel 2003, English version, on win XP sp2)

also I had my settings in excel to go downwards when a enter is pushed. perhaps this might be something in consideration since there are also mother option. didn't had the time yet to try this out.

I will download you newest sheet and will take another look at it.

Cheers!
Jeroen



i tested your latest workbook and it works as should. I asume it is the same code as the previous one. Im not sure why it didn't work. But I like it!!

[mdmackillop]

A variation on the theme!

A revised coding which works by creating temporary hidden worksheets containing all the formatting, which is replaced using PasteSpecial/Formats after any changes have been made. Is this a better approach? Any suggestions?

Bug 1 - The final selected area if you click in and drag cell contents excludes the original cells.

Bug 2 - Try deleting Column A