Drop down lists

[Courtney Elburg]

Hi,
I am creating a drop down of a list that has hundreds of lines. How can I make the drop down dynamic so users do not have to scroll to find their selection? In other words, can I create the ability for users to type the first fews letters of their selection and it finds the word they are are starting to type?
Thank you excels genius's!
Courtney

 

[PeterB]

Hi Courtney,

The best example of this, I have seen is:
http://trumpexcel.com/2013/10/excel-tip-20-create-a-drop-down-list-with-search-suggestions/

Peter.

 

[Hui]

Courtney

Firstly, Welcome to the Chandoo.org forums

See if these links help
http://au.answers.yahoo.com/question/index?qid=20120310110805AAKPsKP
http://www.contextures.com/xlDataVal10.html (Note the section on Autocomplete)

 

[sudipto3003]

the second web link advised by hui is better, but one error is there in the vba code placed in the side. one if condition is not closed at here, the right code is below:

'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
  'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains a data validation list
    Cancel = True
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.TempCombo.DropDown
 
errHandler:
  Application.EnableEvents = True
  Exit Sub
End If 
End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
 
Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End If
 
errHandler:
  Application.EnableEvents = True
  Exit Sub
 
End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, change to KeyUp
 
Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 1).Activate
        Case 13 'Enter
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select
End Sub
'====================================