Prevent paste action in a Worksheet

mond007

New Member
Joined
Oct 9, 2008
Messages
34
Hi <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
<o:p></o:p>
To explain : I have three columns <o:p></o:p>
** with Drop Down Validation based on a known Ranges ** : <o:p></o:p>
<o:p> </o:p>
Location Code, Dept Code and Sub Nom Code <o:p></o:p>
(several hundred rows by the way).<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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-com:eek:ffice:smarttags" /><st1:State w:st="on"><st1:place w:st="on">Col</st1:place></st1:State>) is then set based on this new range and NOT the dummy. The following works great!!!<o:p></o:p>
<o:p> </o:p>
I am using the following code to redefine this e.g dd_dept_code_8 (say for row 8 range).<o:p></o:p>
Code:
[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:p> </o:p>
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:p></o:p>
<o:p> </o:p>
i.e. <o:p></o:p>
Location, Dept, Sub Nom<o:p></o:p>
A100 , D001 , 5000<o:p></o:p>
<o:p> </o:p>
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:p> </o:p>
Thanks in advance. Kuldip
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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?!
 
Upvote 0
Hi

I manage to find a solution in the following thread.

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

Thanks Kuldip
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top