Prevent paste action in a Worksheet


New Member
Oct 9, 2008
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>
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>
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>
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>
[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


Well-known Member
Mar 8, 2004
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?!


New Member
Oct 9, 2008

I manage to find a solution in the following thread.

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

Thanks Kuldip

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...