Problems with Copy/Paste Code

I'm using code that SHG provided for me here; http://www.excelforum.com/showthread.php?t=630464

It worked great in the first workbook I set up but I just set up a new workbook and it's not doing the same thing. For example, it allows me to copy items from column A which have 1 type of validation (restriction on text) to another column which has a formula validation.

It still won't allow me to paste from outside a validated range to inside but I need to prevent people from screwing up the validation between columns. Any ideas?

ChemistB

Hello, Chemist,

I got your note. The prior code verifies that the validation range is the same, but not that the validation itself is unchanged. That would require (I think) storing all the validation information in a static array at initialization, and then checking the validation following any change in the spreadsheet.

I'm swamped this week, and won't have a chance to look at it until this weekend. Perhaps someone will jump in between now and then.

Regards,

Chemist, I hate to punt on you, but doing what I suggested seems like a pain in the neck -- or perhaps lower down ...

Why not instead disable cut and paste (intercept Ctrl+v and purge the clipboard) and drag and drop (save state, disable, and resore on exit).

Or maybe someone else will jump in with an easier solution ...

I'm fine with disabling the pasting and drag and drop functions if I just knew the code to write.

ChemistB

Hi all,
Still looking for resolution here. I have a workbook with conditional formatting and data val in unprotected cells (for manual entry) and unexperienced Excel users. I've put in the instructions to use Paste Special Values but I know someone will accidently put data into the wrong column and cut and paste them to the other column thus screwing up the D.V and C.F.

I'm not picky on how to do this; code that catches the person and doesn't allow paste or code that restores the original formatting (e.g. from a hidden sheet 2). Any thoughts? Thanks

ChemistB

I still need help with this. I tried manually defining the validationrange name using commas to separate different types of vals but then it wouldn't even let me input a value. :-/

Thanks in advance.

ChemistB

Chemist,

Try this, and assign the macro to Ctrl+v

Please Login or Register  to view this content.

When the macro is not present, Excel will revert to its default behavior.

Thanks shg. That works for the group that will use the CNTRL V but a lot of people here use the dropdown menu. Any way to disable that on the menu?
I think those two are the main ways that people without experience will copy data.

ChemistB

I tried this, but it didn't work (??)

Please Login or Register  to view this content.

Sorry, Chemist, I don't mess with menus enough to do this without research.

Thanks for all your help. I'm going to research and I'll let you know if I find anything that works.

ChemistB

Thanks again shg! Your line of code gave me the ID that I needed. (Where would one find these ID's for future reference?) I found this post
http://www.excelforum.com/showthread.php?t=628128

and from there with your macro and your ID, I have this workbook code

Please Login or Register  to view this content.

That disables the paste menu from the tool bar and the right click. I'm happy now.

ChemistB

You did all the heavy lifting, Chemist, thanks for sharing.

Oh -- to answer your question, see http://support.microsoft.com/kb/213552/

Thanks again.