Inspecting cell values in copied range before pasting ...

[Chris L. Beltz]

All,
I have noted that in Excel 2K, the cell validation functionality does
not work for pasted values. In short, I can copy "abc" from notepad
and paste it into a cell requiring a numeric value w/o an objection.

This is a problem for an application I am creating. This application
has a range of values (say A1 to A500) where the user will copy/paste
values into this range from another workbook. These values should all
be numeric, but there is a potential that some will not.

Is there any way to evaluate the values in the copied range (on the
clipboard) before they are pasted? If so, I was thinking that I could
remap ctrl+v to some code which would loop through the values of each
cell in the range, and allow the paste if they are all acceptable.

I know that I could inspect the range using the change event and undo
the paste, but I was wanting to see if there was a "cleaner" way to do
this.

Thanks,

Chris

 

[Frank Kabel]

Hi Chris
one way would be to create a new macro and assign this to a menu button
or shortcut which will do the copying for you. You may take a look at
http://www.rondebruin.nl/copy5.htm

to get started with such a copy routine if this is a feasible way for
you. You can also re-assign the CTRL+V shortcut to this macro. Have a
look at the OnKey method in the VBA help