Jump to content

excel Combo box partial match Code


Recommended Posts

Hello i am quite new to VBA coding but not dumb by any means. 

my combo box currently only searches for exact matches and i cannot seem to get any codes to work which allow partial matches only to be shown. 

E.x

i have a data base list of over 300 line items such as:

HM 200-5

Komatsu 300 LC-8

General Laborer 

construction foreman

Etc...

when i search in the combo box for say "general laborer" i have to type it exactly word for word or nothing happens, it also requires the first parts of the word to be typed out, so even though if i were to type in "laborer" perfectly spelled, general laborer would still not come up because it is not an exact match being that it is missing the "general" part at the beginning. i want to be able to type any part of the word and anything that matches that word to come up on the drop down list for the combo box. so if i were to only type "laborer" General laborer would still come up, or if i were to type "200-5" HM 200-5 would come up. (Currently not the case)

I have seen others codes being posted on forums which were of no help to me at all being that no one ever explains their code, where there data is, what words i may have to change custom for my own worksheet, which i find is lazy and frustrating.

Code im currently using below: 

Dim ws5 As Worksheet
Dim Rng As Range, r As Range
Dim search As String

search = "=" & cboKategorie.Value & "*"

Set ws5 = Worksheets(5)

With ws5

    Set Rng = .Range("A2", .Range("A2").End(xlDown))

    For Each r In Rng

        If r.Value = cboKategorie.Value Then Exit Sub

    Next r

    .ListObjects("tblKategorien").Range.AutoFilter Field:=1, Criteria1:=search
    Set Rng = .Range("A2", .Range("A2").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)

End With

cboKategorie.Clear

For Each r In Rng

    cboKategorie.AddItem (r.Value)

Next r

ws5.ListObjects("tblKategorien").Range.AutoFilter Field:=1

End Sub

So as far as i can See (and of course he doesnt give any explanation about his code and data at all..) his data is on worksheet (5) and his data starts on Cell A2 and continues down. Also what on earth is cboKategorie? a type of code im assuming? or is this specific to his worksheet, that i need to change to be specific for my worksheet? (these are the details i wish people would post with there codes)

I tied this code exactly changing WS5 to WS2 as my data is on Worksheet (2). my data also starts on Cell A5 and continues downward so i changed the Range value from A2 to A5.

Other than that i didnt change a single thing of the code, where did i go wrong? why is it not working, what may i have to change or re-do to make this code work? is there another code which will work better?

Link below to original post and code that i used. (yes i changed the Combo box settings to Match=none and still wasnt able to get any results)

https://stackoverflow.com/questions/34080279/excel-combobox-filter-for-partial-matches/50974296#50974296

any help would be greatly appreciated. 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...