Data Validation source = destination?

[andy62]

I don't know if this is possible:

I would like apply data validation to a range (single column) so that the
items in the drop-down list are generated from any items already entered into
that same range. Kind of like "type ahead" except with data validation. So
upon first use the drop-down shows no data, but then after the first entry
into that range, that entry becomes available in the drop-down, after two
entries they are both available in the drop-down, etc. BUT the items in the
drop-down should be unique: if I have entered (selected) the same text 50
times, I only want it once in the drop-down. Is that possible - without
being too complicated?

TIA.

 

[Stephen]

I don't know if this is possible:

I would like apply data validation to a range (single column) so that the
items in the drop-down list are generated from any items already entered
into
that same range. Kind of like "type ahead" except with data validation.
So
upon first use the drop-down shows no data, but then after the first entry
into that range, that entry becomes available in the drop-down, after two
entries they are both available in the drop-down, etc. BUT the items in
the
drop-down should be unique: if I have entered (selected) the same text 50
times, I only want it once in the drop-down. Is that possible - without
being too complicated?

TIA.

As it's data VALIDATION, how do you think you could ever enter anything in
the first place? If (to start with) there are no entries in the validation
list, you can't enter anything!

 

[Bernie Deitrick]

Andy,

Sure, it is possible, and not really that hard.

You can do what you want, using Data Validation and the worksheet change event. The code below is
written for column B, cells B2:B1000. Change where indicated if you want to do this for a different
range.

HTH,
Bernie
MS Excel MVP

'Put this code into the sheet's codemodule (copy the code, right-click the sheet tab, select "View
Code" and paste the code into the window that appears.)
Private Sub Worksheet_Change(ByVal Target As Range)
'Change the 2 to the desired column number
If Target.Cells(1).Column = 2 Then UpdateValidationList
End Sub

'And put this code into a regular code module:

Sub UpdateValidationList()
Dim myList() As String
Dim myR As Range
Dim myC As Range
Dim myI As Integer
Dim myValList As String

'Change the range address here if needed
Set myR = Range("B2:B1000")

myI = 1
If Application.WorksheetFunction.CountA(myR) = 0 Then Exit Sub

ReDim myList(1 To Application.WorksheetFunction.CountA(myR))

For Each myC In myR.SpecialCells(xlCellTypeConstants)
If IsError(Application.Match(myC.Value, myList, False)) Then
myList(myI) = myC.Value
myI = myI + 1
End If
Next myC

ReDim Preserve myList(1 To myI - 1)

myValList = myList(1)
For myI = 2 To UBound(myList)
myValList = myValList & "," & myList(myI)
Next myI

With myR.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=myValList
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = False
End With
End Sub

 

[andy62]

Thanks. I am getting an error and, I think, missing a piece about setting up
the Data Validation itself.

First the error: I am getting an error "Sub or Function not defined" on this
code: UpdateValidationList

Second, do I need to do anything directly in Data Validation to connect all
this?

Thanks.

 

[Bernie Deitrick]

Andy,

I'm not sure why you are getting an error - everything worked fine for me. Did you put that code
into a regular codemodule?

If you want, I can send you a working example workbook. Just reply to me privately - take out the
spaces and change the dot to . and I will send you the workbook.

HTH,
Bernie
MS Excel MVP

 

[andy62]

Okay, sorry, I had it in "ThisWorkbook", but now have moved it to a regular
module and it seems fine. Thank you!!

Per my other point, I can see all the items by right-clcking on the next
open cell and selecting "Pick from drop-down list . . . ". Is that the
best/only way to access the data? This way doesn't produce a drop-down
arrow, right?

Thanks again!

 

[andy62]

A regular, everyday validation list range which includes a blank cell would
enable a user to either select from the drop-down or create a new item not
already on the list.

 

[Bernie Deitrick]

Andy,

The code should produce data validation with a dropdown arrow (the list
option). When you select the cell, you should not have to right-click the
cell - you should only have to select the cell to see the arrow, and then
click the arrow (to the right of the cell).

HTH,
Bernie
MS Excel MVP