+ Reply to Thread
Results 1 to 33 of 33

Combobox Displayed search

  1. #1
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Combobox Displayed search

    Whilst looking at threads here i came across a combobox that i really like the look of...
    http://www.excelforum.com/excel-prog...-down-list.htm

    I like the way the names auto fill. Though would ilke to have it so say when roc is entered it shows as a dropdown so you can see that there are more than one ROCHESTER.
    Like a search i guess, That would be valid to the first to letters. so if you had name donald, david & daffy when D is entered it showes all three then when DA is showes only David & daffy.

    The project that i will 1st use this on is a contact address ph book.

    Thanks.
    Attached Files Attached Files
    Last edited by D_Rennie; 06-18-2009 at 09:24 AM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Combobox Displayed search

    Hello D_Rennie,

    What was the question?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    1st i would like it to show as a dropdown ehen text is being entered.
    2nd When say A is entered it only shows the A's then when AA is entered it only shows AA.

  4. #4
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    ~~Bump~~~~Bump~~

  5. #5
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    ~~Bump~~~~Bump~~

  6. #6
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Combobox Displayed search

    D_Rennie
    For a "form" Combobox change the Properties Match Type to first letter
    but for a "control Toolbox" combo box hmmm.. dont know if it is possible
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  7. #7
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    Cheers pike, I want to keep it as fmmatchentrycomplete.
    The autocomplete part works fine. Though what i want is for when text is typed into the box the dropdown is shown. And say why R is entered everythink but the R's are removed from the dropdown.
    Ill try and explain better if need be.
    Cheers.

  8. #8
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Combobox Displayed search

    mikerikson was working on that same problem but I dont think he could solve it with out changing the focus and using a Autofilter.. not to sure what can be done in 2007 i have 2003
    Last edited by pike; 06-12-2009 at 09:24 PM.

  9. #9
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    So the coding between 2003 and 2007 is a bit. Ive seen that alot of people dislike 2007.
    Though im yet to see a great advantage of going to 03.
    Im only new to VBE, Though i take it that Learning VBE would also have the same fundamintals as Writing in VB6 for creating stand alone stuff. Either way im enjoing learing this stuff.
    Ill try and see where mikericison got with it.
    Cheers
    Last edited by D_Rennie; 06-12-2009 at 09:01 PM.

  10. #10
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Combobox Displayed search

    he would have created the code himself
    basically you enter a letter in the combox box the focus goes to a dummycontrol
    the list is the filtered using the letter and the results loaded in to the combox for selection. the focus is then set back to combobox .Trick is to only for it to only work for the first letter entry.
    I have a play around to see what i can come up with or moderators may already have sample code/ examples

  11. #11
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Combobox Displayed search

    Hey D_Rennie
    wow it been a bright sunny weekend ... any who...
    try .. and modify
    Please Login or Register  to view this content.
    it basic but will load the a and A's in your list

  12. #12
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    Cheers pike,
    Bright sunny weekend. Long night on sat for me, My better half though that her waters had broken. So a mad dash to the doc. Turns out the waters didnt break.

    Havent played with the code, Though if i understand it correct. It would need to be run for every letter so 26 times. Would that cause it to run to slow. Verry cleaver though, I though about populating 26 colums and going from there.

    Anyhow i think ill through this on the back burner 4 now. After all its only to make the list behave in a pritty manner.

    Got me a copy of VB6. Want to make a program to deal with Accounts payable Via Cheque.
    The idear is have a database with accounts payable to store referances to incoming invoices for each payable invoice approve the payment and print the cheque. There is alot i will need to learn to make to work. some of my other projects have taken the long unnessary way round, though i guess thats learning. And this dosent seam to overwelming for a 1st vb6 project.

    If it sounds like somthink that may intrest you. I need all the help i can get. Ill probley ask dumb question though.
    ]
    Last edited by D_Rennie; 06-16-2009 at 09:56 AM. Reason: Code Tags

  13. #13
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Combobox Displayed search

    !!!! Congratulation !!!! on the new baby
    I'll wait for your return; if it’s a boy and you name him pike I give you all the help I can

  14. #14
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Combobox Displayed search

    Congradulations on the baby!

    I the attached will help with the comboBox question.
    Attached Files Attached Files
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  15. #15
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    :D :D :D No Fellers we didnt have bub yet :D :D :D
    We Are having a little girl in a few weeks "well the scan said to expect a female, Better be otherwise may be a twisted little boy from wearing pink dresses" We chose the name Mia for a girl, Possibley Hunter for a boy Thoguh pike could be a the go.
    Cheers Though I let you know.

  16. #16
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    Wow So simple.
    The talent on here blowes me away.
    Copy of Code
    Please Login or Register  to view this content.

    when A is entered Followed by D it will loop through repopulating. May need to stop the calling of the sub if a letter is present or somethink.[/COLOR]
    Last edited by D_Rennie; 06-16-2009 at 12:14 PM.

  17. #17
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    Please Login or Register  to view this content.
    Came up with this for the loop

  18. #18
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Combobox Displayed search

    Since the comboboxes list doesn't change except via makeCBList, testing the .ListCount should happen after the new list is installed. I would put it inside of makeCBList as below. (The TextBox1.SetFocus/ComboBox1.SetFocus closes the dropdown.)
    Please Login or Register  to view this content.
    One way to test before calling makeCBList would be
    Please Login or Register  to view this content.

  19. #19
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Combobox Displayed search

    I think this is a smoother user interface than the one I posted earlier.
    Please Login or Register  to view this content.

  20. #20
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    Mike works well.

    could you emplain this a little more for me.
    Please Login or Register  to view this content.
    The only thing that i see from the userfrendly side. Is there is no way of knowing what could be in the list untill a letter is typed in (kinda like hide and seek).
    Could on userform initialize, show the the dropbutton and have the list populated with the entire cell range. Or on combobox1_click show the fully populated dropdown.

  21. #21
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Combobox Displayed search

    That section of code runs when there is only one entry in the list that could be a match to the user's typing.
    If the user typed "Dog" and "Dogpatch" was the only entry that matched it, the combobox would show "Dogpatch" with the cursor after g and patch highlighted. The user can Tab out of the field, entering "Dogpatch" or continue typing.



    To fill the un typed list:
    The Initialize event sounds like the place to put that. What code have you tried?
    Last edited by mikerickson; 06-16-2009 at 11:59 PM.

  22. #22
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    Edit edit edit
    Last edited by D_Rennie; 06-17-2009 at 01:10 AM.

  23. #23
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    Please Login or Register  to view this content.
    This code had the repopluating prob.

  24. #24
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    Please Login or Register  to view this content.
    Tryed by setting the focus on UserForm_Initialize to onother object "textbox1" and using comboxbox1_enter to populate the full list, It looks great for not having the dropbuttion.
    Though after it runs the makeCBlist the .droplist is displayed short.

    The otherthing i got stuck on is if the list is populated on UserForm_Initialize how can the makecblist run only on 1st entry. since the list is populated aready. I take it you know what im trying to say here.

  25. #25
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    Please Login or Register  to view this content.
    Im going wrong somewhere. I think the code That you wrote in the red may help it out. Though can get it working "could be a version thing not sure"

    i also tryed to proplate the list with the entire range. when the user presses backspace. nothink worked
    Ive tryed many variations . If combox1.value = "" then ComboBox1.List = Range("A1:A10").Value

    Cheers
    Last edited by D_Rennie; 06-17-2009 at 05:02 AM.

  26. #26
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Combobox Displayed search

    Try this
    Please Login or Register  to view this content.

  27. #27
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    I spent hours playing with this and tryed so many things and still couldnt didnt get it to this stage.

    Only thing i added was
    Please Login or Register  to view this content.
    If that wasent there The List wasent repopulating. I Think even with this there may be issuse adding the code likr that. I have a look 2morow.
    Ill get you to explain some of it later if you want.
    Cheers.

  28. #28
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    Mike it works just like needed.

    also the skiponeevent got remed out.
    With this in place it was not allowing the firing of code makecblist

    I may be wrong but my understanding of the skiponeevent process is.
    It starts with ComboBox1_KeyDown. Soring the data value as a variable true or false "boolean"
    skipOneEvent = (KeyCode = 8) And (Len(ComboBox1.Text) > 1)
    So when a user keys down key 8 "backspace" and combobox1.text length is less that one. it will set the boolean as true.
    So at the same time with ComboBox1_Change()
    if the skiponeevent = true it then sets its boolean value to false stopping any thruther loops and exits the sub stopping the calling of makecblist.

    If my understanding of whats going on is correct there shouldent be a problem. though it seams to be setting the boolean to true on backspace even when combobox1.text is greater than 1.

    now ive remed it out, I have the problem of when the user presses backspace to many times it mucks with the list. Not a real issue though.

    If you dont have the same probem could it be a excell version thing.
    Last edited by D_Rennie; 06-18-2009 at 01:00 AM.

  29. #29
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    Please Login or Register  to view this content.
    Works 99.9% For that extra .1% the use of If ComboBox1.ListIndex = -1 Then May be able to be tweeked.
    Though 99.9% works well for me.

    A Huge Thanks For Letting Me learn off you Guys.

  30. #30
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    I did Say .1% off
    Turns out the .1% was a bugger when using a list like.
    a
    aab
    aaab
    aaaab
    aaaaab
    aaaaaab
    abababab
    abbbba
    abbabab
    abbabaab

    Ive Put some more on the Code To get it at what i call 99.99999% Though You Neverknow

    Final Code (I Think)

    Please Login or Register  to view this content.
    Workbook Example.
    Attached Files Attached Files

  31. #31
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Combobox Displayed search

    What is the problem with a list like that? What does it do/not do that you don't/do want it to.

  32. #32
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Combobox Displayed search

    Thanks for your help Mike ...learnt alot
    Last edited by pike; 06-18-2009 at 08:49 PM.

  33. #33
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Combobox Displayed search

    The issue with a list like
    A
    AAb
    AABA
    was when combobox.matchfound was found there wasent anythink to make the call makecblist.
    All fixed now.

    Ive Added some code to sought the list in alphabetical order. And to find the last populated cell in column A (it will stop on 1st blank cell) Though would like to take it thurther.

    I would like to have another combobox to deal with column B, I would like it to have the same abbilaties as the last. Though have like a lookup function. So when a user enteres A into combobox1, Combobox2 list is populated with bat and batter.
    ....
    Ive never used a lookup function like this. Is it even possable.

    Cheers.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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