Excel empties the clipboard when adding borders! Is there a work-a-round?

[Ed_Collins]

Greetings,

It took me over an hour this morning, but I finally figured out a problem I was having. But now I'm in need of a work-a-round.

I created a VBA spreadsheet that otherwise works fine. But my users quickly found out they couldn't copy data from one worksheet and paste the data into another worksheet. After they copied it, and then after they selected a different worksheet, PASTE and PASTE SPECIAL were greyed out and not available.

They WERE able to paste the data if they temporarily toggled the DESIGN MODE button and turned it on. But I didn't really want them to do that. They WERE able to paste data if they stayed on that same worksheet. But that wasn't what they wanted to do.

Well, here's what was happening: When they selected another worksheet, the Worksheet Activate code for that sheet was naturally first executed. And part of that code clears out border lines from a table:

Please Login or Register  to view this content.

After doing some research and testing, it's this very code that caused the problem! If I bypass it, my users can paste fine. But if this code is executed, they can't.

It seems that when this code is executed, Excel clears the clipboard. That's why PASTE and PASTE SPECIAL were greyed out... because there was no longer anything in the clipboard to paste!

Ideally, I want my users to be able to copy and paste data from one worksheet to another, but also, ideally, I'd like to be able to execute the above code in the Worksheet Activate subroutine... it's part of my code and what I want to do.

Any ideas?

[AlphaFrog]

Perhaps use a different worksheet event other than Worksheet_Activate; maybe Worksheet_Change or Worksheet_Deactivate? I don't understand your requirement to repeatedly clear the borders.

Also, this one line will clear all the borders. It's not a solution though.
Selection.Borders.LineStyle = xlNone