Search a Drop List or Combo box (plz help)

[love_guy_1977]

Hi,

I am looking for something like in this file
http://www.contextures.com/DataValCombobox.zip
http://www.contextures.com/xlDataVal10.html

but with this why

Say my list is:

How are you
Where are you from
What is your name
My name is
.
.
.


If I write "name" in the Combo box, bellow list will appear only

What is your name
My name is


Thank you

 

[Leith Ross]

Hello love_guy_1977,

The macro below will search the range for the entry in the ComboBox and if found fill the ComboBox with matching entries. An example workbook can be downloaded from http://www.mediafire.com/?grbf0pmoj4heuwn

Option Explicit

'Written: March 30, 2011
'Author:  Leith Ross

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
   
  Dim Data() As Variant
  Dim DataRng As Range
  Dim FirstAddress As String
  Dim FoundIt As Range
  Dim LastRow As Long
  Dim N As Long
  Dim What As String
  
    Set DataRng = Worksheets("Sheet2").Range("A2")
    
    LastRow = DataRng.Parent.Cells(Rows.Count, DataRng.Column).End(xlUp).Row
    If LastRow < DataRng.Row Then Exit Sub
    
    Set DataRng = DataRng.Resize(LastRow - DataRng.Row + 1, 1)
    
    What = ComboBox1.Value
    
    If KeyCode = 13 Then
    
       Set FoundIt = DataRng.Cells.Find(What, , xlValues, xlPart, xlByRows, xlNext, False, False)
         If FoundIt Is Nothing Then
            ComboBox1.Value = ""
            ComboBox1.Clear
            Exit Sub
         End If
         
         FirstAddress = FoundIt.Address
         
           Do
             ReDim Preserve Data(N)
             Data(N) = FoundIt.Value
             Set FoundIt = DataRng.FindNext(FoundIt)
             N = N + 1
           Loop While FoundIt.Address <> FirstAddress And Not FoundIt Is Nothing
           
         ComboBox1.List = Data()
         SendKeys "^{F4}"
         
    End If
    
End Sub

Sincerely,
Leith Ross

 

[love_guy_1977]

Hello love_guy_1977,

The macro below will search the range for the entry in the ComboBox and if found fill the ComboBox with matching entries. An example workbook can be downloaded from http://www.mediafire.com/?grbf0pmoj4heuwn


Thank you very much.
It works like this
Write "name" then press Enter

But can be same as the file I give. I mean can be this Combo box as cell value?

Thank you again