How to restrict copy paste in drop down list?

**Parwez** · 12-27-2011, 03:19 AM

I am using Excel 2007 and I have a problem in drop down list. My drop down list contains 15 kinds of ticket types. By default, one can't write anything additional in the given drop down list, but he can copy any cell or text and paste in the drop down list. What I actually want is that one can only be able to copy paste the given 15 ticket types and he can't add anything extra. A user has to fill many rows and it would not be easy for him to select manually from drop down for each row, so I want an user to be able to copy any ticket type from those 15 types and paste in the multiple rows simultaneously. But he should not be able to paste or add anything extra for this drop down column.

In short, I want to allow copy paste but there should be a check in the drop down which does not allow to paste data other than the contents of list.

I hope I am able to make my problem understand to everyone. Please help with the solution/macros.

**DonkeyOte** · 12-27-2011, 07:44 AM

By no means bullet proof but one possible approach:

Please Login or Register  to view this content.

To apply the above - right click on Tab you want to use this on - select View Code and paste into resulting window.

Regards the code:

Modify c_dvrng reference to reflect the range of cells that contains the Data Validation that should persist at all times.

Above assumes that multiple cells within range of interest may be updated simultaneously and need not all contain the same value.

**Parwez** · 12-29-2011, 03:45 AM

Hey...the code worked perfectly fine...solved my problem.

Thanks a lot...

**Parwez** · 12-30-2011, 01:46 AM

I have one more issue...In my data sheet, I have 3 columns (Priority, Modules, Ticket Types) with 3 different drop down lists.
I want to apply the above validation code for all three columns...The problem is that it's allowing user to copy any cell from a drop down and paste in the other drop down column..so the whole drop down list gets changed after pasting....how can we avoid this?

In short, I want the user to be able to copy n paste cell within a drop down column only and can not be able to paste data copied from one drop down column to other different drop down column. Please help me in writing validation codes for all 3 columns.

**namitabhi** · 03-19-2013, 06:52 AM

Hi,

Can anyone help me to restrict the user paste/paste special from drop down list.

For convenience, I am attaching herewith the excel. Need to restrict user in A,D,E,F and I (Highlighted in purple color)

Kindly use the excel "Abhi data"

Thanks in Advance

Abhishek

**arlu1201** · 03-19-2013, 08:46 AM

namitabhi,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.