Search a Drop List or Combo box (plz help)

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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

Code:
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

Board Regular
Joined
Aug 5, 2006
Messages
111
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,875
Messages
5,627,410
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top