Excel 2007 : Dropdown List

Hi Guys,

I have a created a drop down list for a column. It can contain only that values which are specified in the list. My user cannot enter any value , it will give an error. But, when my user copy and paste some value into that section, the data validation is not done, and it allows user to paste any value, even not specified in the drop down list.
I can disallow the paste function, but that will not allow my user to paste any value. I want to allow my user to paste that value, which is there in the drop down list and no other value. Please suggest a solution. I would really appreciate your reply. Please see the attached file for review my problem. Its urgent

Hi and welcome to the board.

Please remember that all participants to this forum help for free on their spare time. So, if your problem is really urgent, maybe see some commercial sites.

copy this code in your work sheet you have your drop down in

Please Login or Register  to view this content.

your welcome --- hope it works for you

This is what I use for dates, names etc.:

Adding a drop down in excel:
• First click on the cell that you want to contain the pull down list
• Next, click on the Data tab and click on “Data Validation”
• Next, select Data Validation and you will get a small options box that pops up
• You need to change the “Any Value” under Allow: from “Any Value” to “List”
• Then in the space labeled “Source” you can type your list by separating each option with a comma.

@grizzly6969: I appreciate your response. When I I copied this code, nothing happened. I don't know why, but everything was like as same before. Would you mind, pasting this code in the worksheet i posted above and attaching that as a solution. Thank you so much for taking time. I will appreciate your reply.

@ Deborah: I understand this way. But I want my user to paste only values that we specified in the "List", he cannot add any other value, by pasting or typing. I appreciate your response though.

@arthurbr: i'm sorry, i didn't realize all these points and posted my question. I will keep this things in mind, for my next posts. Thank you

Here is your workbook with the code --- now do not forget to enable macros -- I tried it and it works

@grizzly6969: Hi, Thanks for replying. sorry to say, but when I opened the above file,it's not working fine on my computer. It's letting me paste anything in the column/cell. There are no restrictions as far as I can see. I enabled the macro, I am not sure whether I'm missing something or why the code is not working on my machine? Thanks. Any, solution would be appreciated.

check your security level --- you can set it to loww just to check it --- if it works set it as trusted site

I could only paste letters that were in your drop down list -- nothing else

maybe you will have to stop cut/copy/paste completely -- as in this work book

just noticed the problem -- if you back space out item in the drop down and paste item in then it will not be accepted but if you paste without back spacing the item will be accepted --- not sure if I can stop this from occuring

Originally Posted by grizzly6969

I could only paste letters that were in your drop down list -- nothing else

I exactly want this, that I could paste only letters in the drop down menu. The code or macro isn't working even when I back space out any cell or not. I think that would help as well.

But the other file where you removed the copy , paste function completely is working fine. the macro is running and I'm not able to copy paste anything.

Thanks a lot.