0

In my worksheet I have hidden columns (col 'CA' & 'CB') which contains dynamically generated values. These are used later in one of our modules for post processing. Values in these columns are unique.

What happens is, that some of our users tend to copy values by selecting full row (e.g. they will select row 15), then pres Ctrl + C to copy, select another row and then press Ctrl + V to paste data (or simply right click on the row and select Insert). This causes that our hidden values are copied as well which disrupts logic of one of our modules.

What we would like to do, is to somehow hook to any of the worksheet events to detect, whether user has done the above mentioned action (copy - paste full row) and if yes, then we want to programatically remove values from our hidden columns.

We have tried to hook events to Worksheet_Change method, but we didn't know how to detect Copy-Paste event.

P.S. we cannot password protect worksheet as we are not the owners of the workbook.

Any help in this matter would be more than appreciated.

Robert J.
  • 2,631
  • 8
  • 32
  • 59
  • 1
    You could play with `Target.Columns.Count` in `Worksheet_Change` to see if every column was selected. This would fire on the `Paste` so you would need to invoke `Application.Undo` to reset the row to the previous values before you start stripping out the values you need to keep. – Scott Holtzman Oct 10 '16 at 20:13
  • We have just tried this, but unfortunately it is also fired on **RowDelete** event (when user wants to remove entire row). Is there a way how to bind specifically to **Paste event**? – Robert J. Oct 10 '16 at 20:15
  • [Here's some juicy stuff that may help ... see all links in question](http://stackoverflow.com/questions/12525942/how-to-detect-paste-event-in-excel) – Scott Holtzman Oct 10 '16 at 20:19
  • Fantastic! make it an answer, please – Robert J. Oct 11 '16 at 07:56
  • Nah. It's just a link I provided from a simple google search. It would be kind of like plagiarism to make it an answer :). Glad you got your solution though. I think the question should be marked as possible duplicate since it was asked already on SO. – Scott Holtzman Oct 11 '16 at 13:49

0 Answers0