+ Reply to Thread
Results 1 to 2 of 2

Two combobox - Search as you type

  1. #1
    Registered User
    Join Date
    03-25-2017
    Location
    Dubai
    MS-Off Ver
    365 ProPlus
    Posts
    42

    Two combobox - Search as you type

    Hello there,

    Kindly help for the following,
    * I have excel sheet attached with tow name lists. one list in sheet "name1" column A, second list in Sheet "name2" column A
    * in Sheet "combo" i want to place two combo box - search as you type (dynamic filtering)
    * i found a code with one combo. its working. but i don't know how to make two of them.
    * i attached both files for your reference.
    * Also please mention the combo box - properties i need to select.

    Thanks in advance.
    PS. I am searching and trying different codes for weeks but somehow i am getting errors. Either its missing the value. Drop down limit to one item only.
    Last edited by nasrulla; 05-24-2018 at 01:45 PM.

  2. #2
    Registered User
    Join Date
    03-25-2017
    Location
    Dubai
    MS-Off Ver
    365 ProPlus
    Posts
    42

    Re: Two combobox - Search as you type

    I manage the above by adding " Application.EnableEvents at the start and end of each combo box code as follows.
    If you guys want to improve or have any comments.

    You are most welcome.

    Option Explicit

    Private Sub ComboBox1_Change()

    Dim d As Object, vList1, i As Long
    Application.EnableEvents = False
    vList1 = Sheets("deList").Range("A2", Sheets("deList").Cells(Rows.Count, "A").End(xlUp)).Value

    With ComboBox1
    If .Value <> "" And IsError(Application.Match(.Value, vList1, 0)) Then
    Set d = CreateObject("scripting.dictionary")
    For i = LBound(vList1) To UBound(vList1)
    If LCase(vList1(i, 1)) Like Replace(LCase(.Value), " ", "*") & "*" Then
    d(vList1(i, 1)) = 1
    End If
    Next
    .List = d.keys
    .DropDown
    End If
    End With
    End Sub


    Private Sub ComboBox1_GotFocus()
    ComboBox1.MatchEntry = fmMatchEntryNone
    ComboBox1.Value = ""
    End Sub

    Private Sub ComboBox1_DropButtonClick()
    Dim vList, d As Object, i As Long
    If ComboBox1.Value = vbNullString Then
    vList = Sheets("deList").Range("A2", Sheets("deList").Cells(Rows.Count, "A").End(xlUp)).Value
    Set d = CreateObject("scripting.dictionary")
    For i = LBound(vList) To UBound(vList)
    d(vList(i, 1)) = 1
    Next
    ComboBox1.List = d.keys

    End If
    Application.EnableEvents = True
    End Sub

    Private Sub ComboBox2_Change()
    Application.EnableEvents = False
    Dim e As Object, vList2, j As Long
    vList2 = Sheets("deList").Range("A2", Sheets("deList").Cells(Rows.Count, "A").End(xlUp)).Value

    With ComboBox2
    If .Value <> "" And IsError(Application.Match(.Value, vList2, 0)) Then
    Set e = CreateObject("scripting.dictionary")
    For j = LBound(vList2) To UBound(vList2)
    If LCase(vList2(j, 1)) Like Replace(LCase(.Value), " ", "*") & "*" Then
    e(vList2(j, 1)) = 1
    End If
    Next
    .List = e.keys
    .DropDown
    End If
    End With
    End Sub

    Private Sub ComboBox2_GotFocus()
    ComboBox2.MatchEntry = fmMatchEntryNone
    ComboBox2.Value = ""
    End Sub

    Private Sub ComboBox2_DropButtonClick()
    Dim vList, e As Object, j As Long
    If ComboBox2.Value = vbNullString Then
    vList = Sheets("deList").Range("A2", Sheets("deList").Cells(Rows.Count, "A").End(xlUp)).Value
    Set e = CreateObject("scripting.dictionary")
    For j = LBound(vList) To UBound(vList)
    e(vList(j, 1)) = 1
    Next
    ComboBox1.List = e.keys

    End If
    Application.EnableEvents = True
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Autofilter or Search as you type in ComboBox
    By Mnet in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-12-2017, 09:16 PM
  2. Search as you type Combobox Excel Userform
    By RJ1969 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2017, 02:51 PM
  3. Replies: 4
    Last Post: 12-02-2014, 05:17 PM
  4. [SOLVED] userform search button remove search only when i type
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-08-2014, 02:32 AM
  5. How to work filters in combobox when i type some some data in combobox
    By suresh mongam in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-29-2012, 04:50 AM
  6. Combobox-listbox multi-type search
    By greekboyuk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2011, 03:47 AM
  7. Is Combobox a vb 6.0 type?
    By munzer1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2007, 05:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1