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.

--
Bob Holmes

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



--
Regards
Frank Kabel
Frankfurt, Germany


Bob Holmes wrote:
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.

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.

--
Bob Holmes MCNGP #31
"Frank Kabel" wrote in message
...
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



--
Regards
Frank Kabel
Frankfurt, Germany


Bob Holmes wrote:
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.

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.

--
Bob Holmes MCNGP #31
"Frank Kabel" wrote in message
...
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



--
Regards
Frank Kabel
Frankfurt, Germany


Bob Holmes wrote:
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.