Data Validation - Do not want to allow pasting into the cell

[MFTiger]

How can a cell with a data validation drop down box not allow users to paste
into the cell. When they paste, it allows values not in the drop-down list.
However the user cannot free-type into the cell anything not in the drop-down
list.

 

[Mike H]

Hi,

That's a shortcoming of data validation that you will have to live with, no
workaround AFAIK.


Mike

 

[NOPIK]

probably, box itself is limited to the list

 

[john]

If you are happy to use a macro you may be able to use Worksheet_Change event
to test user input.

Following code not tested but something along these lines may do what you
want:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim Found As Range

With Target
'row / col of validation list
'change as required
If .Column = 6 And .Row = 7 And .Value <> "" Then

'list range
'change as required
Set rng = Range("C7:C12")

Set Found = rng.Find(Target.Value)

If Found Is Nothing Then

MsgBox "Input Not Valid"

With .Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=$C$7:$C$12" 'change as
required
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Input Not Valid"
.ShowInput = True
.ShowError = True
End With
.ClearContents
End If
End If
End With
End Sub

it's a bit messy perhaps others may be able to offer a cleaner solution.