+ Reply to Thread
Results 1 to 30 of 30

Search listbox with combobox and textbox

  1. #1
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Search listbox with combobox and textbox

    I have cross-posted this at: https://www.ozgrid.com/forum/forum/h...d-with-textbox

    I have a userform named userform1,
    a listbox named listbox1,
    a commandbutton named commandbutton1,
    a textbox named textbox1,
    and a combobox named combobox1.

    I want to sort the listbox with the combobox. So if I select item "122-22-122" in the combobox I only want items with "122-22-122" to show in the listbox.

    I am also trying to input text into textbox1 and press commandbutton1 and filter the listbox.

    So if I enter "555-21-777" in the textbox the listbox only shows items with that number in the listbox.

    All controls are activex controls.

    How can I do this in VBA? All suggestions on where I should start are welcome!
    Last edited by waimea; 02-25-2019 at 12:45 PM.

  2. #2
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Please Login or Register  to view this content.
    This code stops at :

    Please Login or Register  to view this content.
    I don't know why it doesn't work?

  3. #3
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Anyone? I am really struggling with this!

  4. #4
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    997

    Re: Search listbox with combobox and textbox

    Can you attach your sample workbook?

  5. #5
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Hi Akuini,

    thank you for your reply! I am attaching a sample workbook.

    I am trying to search the listbox with a combobox and with a textbox.

    Also, when I double click one listbox item I am opening a second userform.

    On the second userform I want to carry over the product id and product price from userform1 and / or from the rowsource.

    I am also trying to get next / previous products on userform2 using the rowsource table.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    997

    Re: Search listbox with combobox and textbox

    It’s not clear to me, do you want to search for exact match or partial match?
    So if you search "122-22-122" do you expect to get only 1 item ( i.e "122-22-122" ) or possibly more than 1 (probably "122-22-122 abc" and "122-22-122 cde"?

  7. #7
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Hi Akuini,

    thank you for your fast reply. I expect to get possible more than one item.

    But I want to learn so perhaps you could show me how to do both?

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Search listbox with combobox and textbox

    Try this one.
    Combobox1 filters on nmbers in column1. Textbox filters on description in column 2
    So if you start typing 's' you get Soda & Salt, typing 'a' next will filter further to Salt and so on...
    Attached Files Attached Files
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  9. #9
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Hi bakerman2,

    thank you for your reply! The code works but I need to test it more so that I can understand it!

  10. #10
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Search listbox with combobox and textbox

    Didn't read textbox part so replace Textbox1_Change to this one to respond to numbers in column1.
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Hi again,

    thank you again for your reply! It works really well.

    What would be the best way to reset the listbox to it's default state?

  12. #12
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Search listbox with combobox and textbox

    For the texbox it's easy.
    Change this line
    Please Login or Register  to view this content.
    to this.
    Please Login or Register  to view this content.
    So clearing all numbers will give full list in ListBox.

    For the Combobox put this as first line in Change-event.
    Please Login or Register  to view this content.
    Clearing all numbers in Combobox will restore list in ListBox
    Last edited by bakerman2; 02-26-2019 at 02:09 AM.

  13. #13
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    This is working really well now with the textbox reverting the llistbox!

    Did you see my double click event on the listbox?

    Do I have to declare any public variables to use the rowsource from userform1 into userform2?

    I would like to be able to pass the same row number in the rowsource into userform2.

  14. #14
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Search listbox with combobox and textbox

    In Userform2
    Please Login or Register  to view this content.
    PS: Did you read my previous post regarding the Combobox resetting the ListBox ?

  15. #15
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Hi bakerman2,

    I read your previous post on resetting the combobox and it keeps getting better and better!

    And perhaps there should be a test in userform2 if userform1 is not loaded?

    The last question I have is how to go to next/previous products in userform2?

  16. #16
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    I got it to work. I am still interested in how I can go from one product to another product in userform2?
    Last edited by waimea; 02-26-2019 at 03:44 AM.

  17. #17
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Search listbox with combobox and textbox

    Do you want the Previous/Next from a filtered list or from Entire listdepending on when you activate Userform2.

    Guess you're gonna say both.

  18. #18
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Hi bakerman2,

    thank you for your reply. You are a good guesser and you are right, I would like to see both so that I can learn.

    Please Login or Register  to view this content.
    I added a combobox and I am trying to sort on a different column then the first one, it this possible?

  19. #19
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Quote Originally Posted by bakerman2 View Post
    In Userform2
    Please Login or Register  to view this content.
    PS: Did you read my previous post regarding the Combobox resetting the ListBox ?
    Hi,

    I am getting a runtime error 381 when I try to initialize values in userform2 and I dont' know why?

    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    I am still trying to figure out how to get next and previous buttons.

    All suggestions are welcome?

  21. #21
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Bakerman2,

    could you please help me with this? I have tried the entire day without success.

  22. #22
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Search listbox with combobox and textbox

    Sorry for late response but didn't have much time past few days.

    Try this for starters.
    Attached Files Attached Files

  23. #23
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Hi bakerman2,

    thank you for your reply, it is very much appreciated!

    I am going to take a look at your code and see if I understand it!

    I want to be able to understand how to pass values from userform to another userform.

    It seems to be working really well!

  24. #24
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    I have looked at your code and it works with one little thing, if I click next product a couple of times it gives me an error.

    Please Login or Register  to view this content.

  25. #25
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Search listbox with combobox and textbox

    I was compensating for a filtered list.

    Replace Next-button code in Userform2 with this.
    Please Login or Register  to view this content.

  26. #26
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Hi bakerman2,

    this is very kind of you and I am glad that you could help me.

    Thank you again.

    Please Login or Register  to view this content.
    I still have a question about your code for combobox change, would it be possible to adapt it to use column(A or column F) ?

    Please Login or Register  to view this content.
    I am guessing this is the part that I need to change?

  27. #27
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Search listbox with combobox and textbox

    Okay, this is the last one for this thread because we're way out of scope from your initial question.

    A ListBox starts at index 0 so Column F would be
    Please Login or Register  to view this content.

  28. #28
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Hi bakerman2,

    thank you again for your time and your code. I have learned a lot from your code!

  29. #29
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Search listbox with combobox and textbox

    Glad to help and thanks for rep+.

  30. #30
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Sweden
    MS-Off Ver
    2016.
    Posts
    397

    Re: Search listbox with combobox and textbox

    Hi again bakerman2,

    I have your code all working but I am looking at how I would filter using bigger then or smaller then x in a combobox.

    In waimea_II.xlsm‎ that you uploaded the column D contains quantity.

    Say I would like to filter on greater than "1000" or greater then 4000?

+ 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. how can i search in listbox by combobox
    By alma_fandi in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-29-2018, 05:58 PM
  2. Filter UserForm Multicolumn ListBox with TextBox/ComboBox
    By Alex.riccio in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-07-2016, 03:12 PM
  3. Filter UserForm Multicolumn ListBox. populated by RowSource. with TextBox/ComboBox
    By Alex.riccio in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-07-2016, 10:19 AM
  4. [SOLVED] Filter UserForm Multicolumn ListBox with TextBox/ComboBox
    By Alex.riccio in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2016, 04:31 PM
  5. Search Data using Combobox Textbox and Listbox
    By Adrastia in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-31-2012, 05:27 AM
  6. Search listbox from textbox
    By zplugger in forum Excel General
    Replies: 12
    Last Post: 08-23-2012, 08:11 PM
  7. [SOLVED] Textbox to search listbox
    By zplugger in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-22-2012, 08:25 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