Recognizing something is being pasted in code.

B

Bob Holmes

Hello,
I have used a worksheet as a data entry screen. I want to allow only
certain entries in the first column so I perform a validation against a
pre-defined list within the 'Worksheet_Change' event. If the entry is
valid, this entry is used to fill in other cells with associated data.
However, I have run into the possibility that the user is copying a number
of rows from another worksheet and trying to paste them into mine. This is
something that I want them to be able to do. Unfortunately, when there is
more than one row being pasted, my code fails. Is there a way to recognize
that the user is pasting so that I might bypass the validation in this
instance and perform the validation later? Or, to see that the 'Target' in
the 'Worksheet_Change' event is more than one cell? If the answer is no to
both of these, would anyone have any other suggestions? I am open to all
possibilities. Thanks for your time.
 
F

Frank Kabel

Hi Bob
best way: always post your code :)

some ideas:
if target.cells.count > 1 then
msgbox "more than one cell"
application.undo
end if

or loop through target:

dim cell as range
for each cell in target
if cell.value<>"test_value" then
msgbox "value in cell " cell.address & " is not o.k"
application.enableevents=false
cell.clearcontents
application.enableevents=True
end if
next cell
 
B

Bob Holmes

Thanks a lot Frank. I'm going to try out your code and see if I can get it
to work for my purposes.
Sorry about not posting code. I thought that I'd find out if I was looking
for something that was possible first and then supply code if needed. I
really appreciate all of the information.
 
B

Bob Holmes

Thank you very much, Frank. This will work fine. I can't believe it was as
simple as getting a count of the cells. The test of cells with the range
will work for other aspects of my project as well. You're a lifesaver.
 
Top