Restrict Copy Paste in Drop Down list

[Parwez]

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.

 

[Jaafar Tribak]

Hi & welcome to the forum.

This should prevent from pasting values that are not in the validation list into the cell :

Place code in the worksheet module :

Private Const ValidCell = "F4" 'change cell as required.


Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next
    If Not Intersect(Target, Range(ValidCell)) Is Nothing Then
        If WorksheetFunction.Match _
        (Target, (Range(Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1))), 0) = Empty Then
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        End If
    End If

End Sub

 

[Jaafar Tribak]

You may also want to take a look @ Post#6 in this thread for a more robust solution which works for dragging data as well.

 

[Parwez]

Hi Jaafar,

Thanks a lot for reply...I tried this code but it actually freezes my cell containing the drop down list. It does not even allow to select other value from the list.

I also tried your longer code posted on the different thread as suggested by you, but it's giving some range error.

Please help me with the right code. Thanks in advance..

Hi & welcome to the forum.

This should prevent from pasting values that are not in the validation list into the cell :

Place code in the worksheet module :

Private Const ValidCell = "F4" 'change cell as required.
 
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    On Error Resume Next
    If Not Intersect(Target, Range(ValidCell)) Is Nothing Then
        If WorksheetFunction.Match _
        (Target, (Range(Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1))), 0) = Empty Then
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        End If
    End If
 
End Sub