sorting through a dropdown list by letter

  • Thread starter Thread starter mmay321
  • Start date Start date
M

mmay321

is there a way to set up an in-cell dropdown list so that when the use
selects a letter on the keyboard, the list jumps to the first list ite
that starts with the selected letter
 
This is not built in to excel, have a look here for a workaround

http://www.contextures.com/xlDataVal10.html
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
Hi mmay 321,

Here is a not so elegant possible way to do that. Assumes the letter will
be entered in A1 and the list is in column H and sorted alphabetically. In
this example everything that starts with an A is from H1 to H5, the B's are
from H6 to H17 and the C's are from H18 to H26. You would have to do the
entire alphabet from Sub AList to Sub ZList and hard code the ranges for
each letter. If the list is very dynamic that would present some problems,
but if it changed very little this might get you there.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("A1") Then Exit Sub
If Range("A1").Value = "a" Then
AList
ElseIf Range("A1").Value = "b" Then
BList
ElseIf Range("A1").Value = "c" Then
CList
End If
End Sub

Sub AList()
With Range("F1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$H$1:$H$5"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Range("F1").Select
End Sub

Sub BList()
With Range("F1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$H$6:$H$17"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Range("F1").Select
End Sub

Sub CList()
With Range("F1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$H$18:$H$26"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Range("F1").Select
End Sub

HTH
Regards,
Howard
 
Data|Validation doesn't support this.

But a combobox from the control toolbox toolbar would.

I'd set the style to: fmstyledropdownlist
and change the matchentry to: fmmatchentrycomplete

While in design mode--also an icon on that control toolbox toolbar, right click
 
Back
Top