Filtering a Drop Down Box

[dazfoz]

Hi All,

Is there any way that a validation drop down list can be set to jump to a letter following after hitting it on the keyboard (As with many form entry cells on internet that have lists) e.g. hitting 'S' will default the list to the entries beginning with 'S', immediatly after, hitting 'K' will then go to the entries beginning with 'K' (i.e. it will not look for entries beginning with SK).

Thanks

 

[dazfoz]

Oh,

Im using 2003!

 

[VoG]

Try this http://www.contextures.com/xlDataVal14.html

 

[MickG]

Try this:-
If your Validation Box is in "D1", and you validation range is column "A", then If you type in "D1" the first letter of the range of words you want returned then this code should return that range.
NB:This code uses Column "Z" as a holding column.
In you right tClick the sheet tab, select "View Code" . VB Window appears , Paste code into VB window.
Close window
Enter Letter in "D1" to run code..

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
    [COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "D1" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Len(Target) = 1 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] rng
                [COLOR="Navy"]If[/COLOR] UCase(Left(Dn, 1)) = UCase(Target) [COLOR="Navy"]Then[/COLOR]
                    c = c + 1
                    Cells(c, "Z") = Dn
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Set[/COLOR] nRng = Range("Z1").Resize(c)
    [COLOR="Navy"]With[/COLOR] Range("D1").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=" & nRng.Address & "", AlertStyle:=xlValidAlertStop
        .ShowError = False
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Else[/COLOR]
[COLOR="Navy"]With[/COLOR] Range("D1").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:="=" & rng.Address & "", AlertStyle:=xlValidAlertStop
    .ShowError = False
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[dazfoz]

VOG,

Thanks for pointing me in the right direction, that solution is kinda working, the problem is that I have drop down box's in merged cells and the code works in single cells, but not for the merged ones, any ideas?

'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler

If Target.Count > 1 Then GoTo exitHandler

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

  On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains a data validation list
    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 + 15
      .Height = Target.Height + 5
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If

exitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
errHandler:
  Resume exitHandler

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
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

 

[VoG]

Avoid merged cells! See http://www.mrexcel.com/forum/showthread.php?t=532847

 

[dazfoz]

Genius!

Thanks for the help.