Prevent paste action in a Worksheet

[mond007]

Hi <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I need to prevent a paste action in 3 columns in a worksheet as it removes formatting and Data Validation and the drop down values get lost. <o></o>
<o></o>
To explain : I have three columns <o></o>
** with Drop Down Validation based on a known Ranges ** : <o></o>
<o> </o>
Location Code, Dept Code and Sub Nom Code <o></o>
(several hundred rows by the way).<o></o>
<o></o>
Through code I have setup a dummy (one blank cell) drop down for the 2nd and 3rd Column (i.e. for Dept Code and Sub Nom Code as there is a hierarchy). The Dept can only be from a Drop Down list based upon a known list for that location Code in a range and likewise for Sub Nom Code based upon a known list for that Dept Code.<o></o>
<o></o>
Upon Selecting a Location Code (also a drop down) I am “redefining” the range for the Dept Code (Worksheet Change) to be a new range and the Data Validation (for 2<SUP>nd</SUP> <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:State w:st="on"><st1lace w:st="on">Col</st1lace></st1:State>) is then set based on this new range and NOT the dummy. The following works great!!!<o></o>
<o> </o>
I am using the following code to redefine this e.g dd_dept_code_8 (say for row 8 range).<o></o>

[COLOR=black][FONT=Courier New]dd_dept_code_name_range = "dd_dept_code_" & irows<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]On Error Resume Next<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]ThisWorkbook.Names(dd_dept_code_name_range).Delete<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]ThisWorkbook.Worksheets("Supplier Input Sheet").Cells(i_rows, 6).Select<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]DoEvents<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]With Selection.Validation                   'ADD DEPT VALIDATION<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]        .Delete<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]                                                    xlBetween, Formula1:="=" & dd_dept_code_name_range<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]End With<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]DoEvents[/FONT][/COLOR]

<o> </o>
THE PROBLEM : I have told users to ‘paste special’ but when users paste (and trust me they will) I loose the drop downs for the 2<SUP>nd</SUP> and 3<SUP>rd</SUP> columns. <o></o>
<o> </o>
i.e. <o></o>
Location, Dept, Sub Nom<o></o>
A100 , D001 , 5000<o></o>
<o> </o>
Is there a way to prevent paste only in these three columns. i.e. trap for paste action and give a message box and say “You are not allowed to paste – please use paste values.” ?
<o> </o>
Thanks in advance. Kuldip

 

[AgentSmith]

I have a nagging feeling I've come across this on this forum before! Basically the idea is to use the Worksheet_Change event to Undo any Paste actions. Then you can do a matching PasteSpecial action instead. In pseudo-code this would work as follows:

Worksheet_Change event
Look at the last entry in the undo stack.
If that says "undo paste" then proceed.
If the Target (parameter in this event) intersects your "protected" range then proceed.
Undo the last action.
Mimic the paste action but use PasteSpecial instead.

... hope this gives some ideas?!

 

[mond007]

Hi

I manage to find a solution in the following thread.

"Preserve formatting when pasting into a cell" in this forum.

Thanks Kuldip