VB Script Excel Copy Issue

Question

Hello,

I am not sure if there is a solution to this issue. In Excel when you copy a cell the border starts moving/flashing. Only when that border is moving you are allowed to paste its contents into another cell. Once the border is no longer moving, you are also unable to paste its contents. The other option is to double click on the cell or go into the formula bar to manually copy the data, which then you can paste as much as you want, at least until something else is copied.

Here is my problem. I have script within the “Private Sub Worksheet_SelectionChange(ByVal Target As Range)” section of my worksheet. So every time another cell is selected this script is run. The script works perfectly and I do not need this changed. The spreadsheet first needs to be unlocked before the script can run, so I had to put this at the top of the script “ActiveSheet.Unprotect” and this at the bottom “ActiveSheet.Protect”. When I select a different cell and the code hits either the unprotect or the protect commands, then the copied cell border is no longer flashing, so I can’t paste.

Is there a script of some sort that I can use so it retains the copied data? The script should only run when a cell is copied. I also don’t want to use the SendKeys function because that typically causes more issues then what it solved. I have always been wondering where Excel stores its data for copied cells, since usually copied data is stored in the windows clipboard, or some other dumping ground since Windows 7 & 8 no longer use the clipboard for copping data. So I don’t understand why Excel doesn’t use the same method as everything else when a cell is copied.

This isn’t too much of an issue, more of an annoyance than anything. If someone should have a solution to this please let me know, but I don’t think there is one.

Thanks,

Chris

Answer 1

Hi Rick,

To see my code please visit this link and view the answer that I listed to my own question.

http://stackoverflow.com/questions/17005931/excel-2010-vb-script-highlight-row-issue/17270160?noredirect=1#comment25044450_17270160

I don't think your solution that you provided will work. I am not actually issuing a copy command within the VBScript at all. When I say copy I mean just your typical Ctrl + C, or edit menu and selecting copy. I know your code would work if I was using script for the copy, however I'm just referring to the typical Ctrl + C method. Once the script hits the unprotect row it prevents the cells from being copied.

I do have a ton of other code that I am using not listed here, however I can guarantee you that it is due to the unprotect feature. I walked through that code step-by-step and as soon as it hits the unprotect line it prevents the cell from being copied. If I run the spreadsheet with only this code and remark the protect and unprotect lines, I do not have an issue with the copy.

Thanks for the suggestion, I really do appreciate it, please let me know if you have any further suggestions.

I'm sorry if this reply looks weird, I am doing this on my iPhone.

Thanks,

Chris

Answer 2

Without seeing your code, it is hard to give you an answer I can be sure of; however, maybe using a global variable would work for you. To declare a global variable, declare it as Public in a general Module outside (and before) of any procedures that you might have in the module. So, simply put something like this on a line all by itself at the top of the module...

Public CopyCell As Range

Then, in whatever procedure you now issue the Copy command from, do this instead...

Set CopyCell = Range("C3")

In the above, C3 is the assumed cell you are copying... use the reference method you now use to identify the cell. Next, in whatever procedure you now issue the Paste operation, do this instead....

CopyCell.Copy Range("D4")

In the above, D4 is the assume cell you are copying to... use  the reference method you now use to identify the cell.