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.

Hi,

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


Mike

"MFTiger" wrote:

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

On Nov 19, 8:41*pm, MFTiger <MFTi...@discussions.microsoft.com> wrote:

probably, box itself is limited to the list

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


"MFTiger" wrote:

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