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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

AgentSmith

Well-known Member
Joined
Mar 8, 2004
Messages
575
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

New Member
Joined
Oct 9, 2008
Messages
34
Hi

I manage to find a solution in the following thread.

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

Thanks Kuldip
 

Watch MrExcel Video

Forum statistics

Threads
1,122,959
Messages
5,599,056
Members
414,281
Latest member
Engjamal2021

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
Top