List item Based on Cell value

  • Thread starter Thread starter Soniya
  • Start date Start date
S

Soniya

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




.
Click to expand...
 
Back
Top