Excel 2007 : Dropdown List

[yaman1510]

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

[arthurbr]

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.

[grizzly6969]

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

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim UndoString As String
    Dim srce As Range
    Dim trgt As Range

    On Error GoTo err_handler

    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Left(UndoString, 5) = "Paste" Then 'Only allow Paste Special|Values
            Application.ScreenUpdating = False
            
            Application.EnableEvents = False
            Application.Undo
            Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            Application.ScreenUpdating = True
            Application.EnableEvents = True
            
    ElseIf UndoString = "Auto Fill" Then
    
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Set trgt = Selection
        
        Application.Undo
        
        Set srce = Selection
        
        srce.Copy
        
        trgt.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                    
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        
    End If

    Exit Sub

err_handler:

End Sub

[grizzly6969]

your welcome --- hope it works for you

[Deborah Martin]

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.

[yaman1510]

@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

[grizzly6969]

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

[yaman1510]

@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.

[grizzly6969]

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

[yaman1510]

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

Hey I checked the security level and I enabled everything. nothing works. Were you able to disallow user to paste any value, other than the one specified in the drop down list, when you used the same code in your machine??
The code/macro is not working on my machine...but the other macros are working fine.
Thanks a lot.

[grizzly6969]

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

[yaman1510]

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.

[grizzly6969]

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

[grizzly6969]

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