List item Based on Cell value
 

Hi All,

I have a data validation list in a cell having more than
500 items.

is it possible to have selected items to show on the drop
down list based on the first letter/letters i enter in
the cell? My list is already sorted alphabatically.

like if i type M in my cell the dropdown list show only
items starting M and in case i enter ME all items
startinf ME and like wise..?

TIA

Soniya

List item Based on Cell value
 

Soniya,

If the source of the list was an Excel range, you could use worksheet event
code to take the cell input (it could be 1, 2 or more chars), and find all
items starting with that value and populate the Data Validation list with
it.

Thing is, you could not use the same cell as the DV as it will fail
validation, so the start string would need to be another cell. Is that okay?
If so, I will knock up some code to demonstrate it.

--

HTH

Bob Phillips

"Soniya" wrote in message
...
Hi All,

I have a data validation list in a cell having more than
500 items.

is it possible to have selected items to show on the drop
down list based on the first letter/letters i enter in
the cell? My list is already sorted alphabatically.

like if i type M in my cell the dropdown list show only
items starting M and in case i enter ME all items
startinf ME and like wise..?

TIA

Soniya

List item Based on Cell value
 

Soniya,

Here's the code. First a few fundamentals

- this assumes that the Data Validation cell is F7, there is a variable at
code that can be changed
- the selector cell is E7, ditto
- Sheet2 column N is available to store a temporary range

Add this code to the worksheet code module (right-click on the tab name,
select View Code, and copy the code into the code pane)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cInput As Long, cValues As Long
Dim i As Long, j As Long
Dim vValue
Const dvCell As String = "F7"
Const selectCell As String = "$E$7"
Const listSheet As String = "Sheet2"

Application.EnableEvents = False
If Target.Address = selectCell Then
Worksheets("Sheet2").Columns(14).ClearContents
cInput = Len(Target.Value)
cValues = Worksheets(listSheet).Range("myRange").Cells(Rows. Count,
1).End(xlUp).Row
j = 1
For i = 1 To cValues
vValue = Worksheets(listSheet).Range("myRange").Cells(i,
1).Value
If LCase(Left(vValue, cInput)) = LCase(Target.Value) Then
Worksheets(listSheet).Cells(j, "N").Value = vValue
j = j + 1
End If
Next i
End If

If j 1 Then
ThisWorkbook.Names.Add Name:="shortRange", RefersTo:="=" & listSheet
& "!$N$1:$N$" & j - 1
With Range(dvCell).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=shortRange"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range(dvCell).Value = Worksheets(listSheet).Range("N1").Value
Range(dvCell).Select
End If

Application.EnableEvents = True

End Sub

You can easily add DV input and error messages.

Tested on XL2000 with XP Pro.

--

HTH

Bob Phillips

"Soniya" wrote in message
...
thanks Bob,

My Validation Formula look like this
=INDIRECT("Sheet2!MyRange")

MyRange is dynamic

and my input cell is in Sheet1


TIA

Soniya

-----Original Message-----
Soniya,

If the source of the list was an Excel range, you could
use worksheet event
code to take the cell input (it could be 1, 2 or more
chars), and find all
items starting with that value and populate the Data
Validation list with
it.

Thing is, you could not use the same cell as the DV as
it will fail
validation, so the start string would need to be another
cell. Is that okay?
If so, I will knock up some code to demonstrate it.

--

HTH

Bob Phillips

"Soniya" wrote in message
...
Hi All,

I have a data validation list in a cell having more
than
500 items.

is it possible to have selected items to show on the
drop
down list based on the first letter/letters i enter in
the cell? My list is already sorted alphabatically.

like if i type M in my cell the dropdown list show only
items starting M and in case i enter ME all items
startinf ME and like wise..?

TIA

Soniya


.

List item Based on Cell value
 

Thanks Bob,

Soniya



-----Original Message-----
Soniya,

Here's the code. First a few fundamentals

- this assumes that the Data Validation cell is F7,
there is a variable at
code that can be changed
- the selector cell is E7, ditto
- Sheet2 column N is available to store a temporary range

Add this code to the worksheet code module (right-click
on the tab name,
select View Code, and copy the code into the code pane)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cInput As Long, cValues As Long
Dim i As Long, j As Long
Dim vValue
Const dvCell As String = "F7"
Const selectCell As String = "$E$7"
Const listSheet As String = "Sheet2"

Application.EnableEvents = False
If Target.Address = selectCell Then
Worksheets("Sheet2").Columns(14).ClearContents
cInput = Len(Target.Value)
cValues = Worksheets(listSheet).Range
("myRange").Cells(Rows.Count,
1).End(xlUp).Row
j = 1
For i = 1 To cValues
vValue = Worksheets(listSheet).Range
("myRange").Cells(i,
1).Value
If LCase(Left(vValue, cInput)) = LCase
(Target.Value) Then
Worksheets(listSheet).Cells
(j, "N").Value = vValue
j = j + 1
End If
Next i
End If

If j 1 Then
ThisWorkbook.Names.Add Name:="shortRange",
RefersTo:="=" & listSheet
& "!$N$1:$N$" & j - 1
With Range(dvCell).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=shortRange"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range(dvCell).Value = Worksheets(listSheet).Range
("N1").Value
Range(dvCell).Select
End If

Application.EnableEvents = True

End Sub

You can easily add DV input and error messages.

Tested on XL2000 with XP Pro.

--

HTH

Bob Phillips

"Soniya" wrote in message
...
thanks Bob,

My Validation Formula look like this
=INDIRECT("Sheet2!MyRange")

MyRange is dynamic

and my input cell is in Sheet1


TIA

Soniya

-----Original Message-----
Soniya,

If the source of the list was an Excel range, you
could
use worksheet event
code to take the cell input (it could be 1, 2 or more
chars), and find all
items starting with that value and populate the Data
Validation list with
it.

Thing is, you could not use the same cell as the DV as
it will fail
validation, so the start string would need to be
another
cell. Is that okay?
If so, I will knock up some code to demonstrate it.

--

HTH

Bob Phillips

"Soniya" wrote in message
...
Hi All,

I have a data validation list in a cell having more
than
500 items.

is it possible to have selected items to show on the
drop
down list based on the first letter/letters i enter
in
the cell? My list is already sorted alphabatically.

like if i type M in my cell the dropdown list show
only
items starting M and in case i enter ME all items
startinf ME and like wise..?

TIA

Soniya


.



.