+ Reply to Thread
Results 1 to 29 of 29

Populate listbox

  1. #1
    Forum Contributor
    Join Date
    10-18-2018
    Location
    Amsterdam, Holland
    MS-Off Ver
    2013
    Posts
    104

    Populate listbox

    Hi all,

    I got this excell document with search textboxes.

    The quick search works like it should. I typ a search term in and it looks in the columns ID, productname and quantity per unit.
    Can someone tell me how i add another column? I can't find the line in the code that determines in how many columns it has to look.

    My second problem is that my advanced search doesn't work as it should. It searches but as soon as i delete the search term and the textbox txtID is blank my listbox is empty.
    I would like that when the search term is removed that the listbox is filled with all valuables like it is when it's opened.

    Can someone help me with this?
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    Hi, welcome to the forum
    Try this one
    Attached Files Attached Files
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    I did not look at the search columns but this is step 1

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    Well maybe this is better
    All searches work
    Enjoy your weekend
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    10-18-2018
    Location
    Amsterdam, Holland
    MS-Off Ver
    2013
    Posts
    104

    Re: Populate listbox

    That works like a charm. Looks like an easy solution.
    Is it also to search for an ID and product at the same time? And if there are no results i will get a message box?
    Or do i have to change the whole code then?

    Curious how i can expand this excell so i can add more columns, this is just to try out. Once i know how to search in more columns (say i would to add year) i can expand it with even more columns to search in.

    Thanks in advance!

  6. #6
    Forum Contributor
    Join Date
    10-18-2018
    Location
    Amsterdam, Holland
    MS-Off Ver
    2013
    Posts
    104

    Re: Populate listbox

    This works less ;(

    When i search on ID and i typ "1" i get results that contain no "1" in it

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    It searches for all occurrences of what you type, not just the ID, that will need fine-tuning but this was just to show how it can be done too, if you type a year or type reviewed
    If you really want it to work with message boxes and so then you'll have to review the code, sections can be reused but you will have to 'attack' it in another way.

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    Minor modification but not what you want yet
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    10-18-2018
    Location
    Amsterdam, Holland
    MS-Off Ver
    2013
    Posts
    104

    Re: Populate listbox

    Ok, clear for me. I will think about it

    As last; can you tell me in what line code i can change the number of columns the listbox shows and where i can change in how many columns the search funtion "looks" ?

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    I assume you did not write this code yourself.
    The listbox only allows one column but the content is built for two.
    You should check how to use multi column Listboxes.

    The search is the extra function I added, if concatenates the columns as one string to look in to.
    I'm going out now but I'll take a look some time this weekend and upload a test.
    Meanwhile,
    happy coding

  11. #11
    Forum Contributor
    Join Date
    10-18-2018
    Location
    Amsterdam, Holland
    MS-Off Ver
    2013
    Posts
    104

    Re: Populate listbox

    True, i look on the internet find things and try to learn myself to understand the code, which is hard.

    Thank you

  12. #12
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    That’s the way I started +25 years ago😜

  13. #13
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,241

    Re: Populate listbox

    Keebellah good idea
    Last edited by daboho; 10-27-2018 at 10:37 AM.
    "ThankyouFor Attention * And Your Help!!"

  14. #14
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    I rewrote most of your code

    Have fun
    Attached Files Attached Files

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    I can imagine all the next questions and wishes you could have...
    Adding records, Editing records, Deleting record, Sorting records by ID, Name, Lotnummer, Year etc.
    With that in mind a minor addition, and for you brain gymnastics and build-up your coding experience.

    This is the way I attack these challenges, I'm sure others have other ways and better ideas, but ... I never learned for this, it's built on experience
    Attached Files Attached Files

  16. #16
    Valued Forum Contributor dotchiejack's Avatar
    Join Date
    05-21-2015
    Location
    Antwerp,Belgium
    MS-Off Ver
    2016
    Posts
    507

    Re: Populate listbox

    I was following this topic.
    For 2 reasons, 1.maybe I can learn something. and 2 Topic starter is Dutch, I am from Belgium and I speak Dutch.
    So I made my own example.
    I always use real tables when I can.
    The first search frame is search ID or Productname or Reviewd.
    The second search frame is search ID and Productname and Reviewd. I don't think this is necessary but I put it in there as an example.
    Furthermore you can add new products, edit products, delete products.
    Hope it helps
    Have a nice weekend.
    Attached Files Attached Files
    Click the * Add Reputation below to say thanks.

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    Hi, I started for the same reason, the helping part and of course learning from other one's questions.
    I prefer real tables but always leave it to the OP to decide.
    I'll take a look at your file, sounds good.
    Yes, I'm Dutch but grew up outised of Holland, only came to live here during my studies and then settled here after 1987

  18. #18
    Forum Contributor
    Join Date
    10-18-2018
    Location
    Amsterdam, Holland
    MS-Off Ver
    2013
    Posts
    104

    Re: Populate listbox

    Well, thank you very much!

    From what i've seen now it looks very nice.
    You perhaps know why i can't type in txtProd? I checked if it's locked but it isn't

    Can you please tell me in what line of code it determines in how many and which colums it searches for data?

    Ofcourse i wanna add more and i would like to try to search on Year.

    I have problems with understanding this:
    Please Login or Register  to view this content.
    Guess i have to add something similair if i wanna search on year
    Last edited by Bandito1; 10-28-2018 at 02:50 PM.

  19. #19
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    I checked, my mistake, you can type now
    I'll explain the Case = ... lines later.
    Have you checked dotchiejack's file? It's very nice and a good approach.
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    10-18-2018
    Location
    Amsterdam, Holland
    MS-Off Ver
    2013
    Posts
    104

    Re: Populate listbox

    Thank you very much.
    Now ID is acting strange, i can't use all numbers in it and if typ "1" in it i can't add any other number than "8"..

    Yes, and nice to see 3 dutch people in a thread without a word Dutch

    I will see which approach fits my wishes the most. Till now i didn't plan to add and edit records since i wont add records. Other people are doing that at this moment and i don't know yet if they are ready for a change
    Last edited by Bandito1; 10-28-2018 at 04:09 PM.

  21. #21
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    What it foes is when nothing you type matches the last entry is removed, that is what the IF condition does.
    The Function returns a true or false based upon the fact that it found a value or not.
    18 is the highest ID you have so if you type 19 nothing is there.
    When you type 1 you will see all the IDs with a 1 in it when you type 18 you will see two, (duplicate IDs) and if you type 181 mothing happens becasue there is no 181 and the text remains 18

  22. #22
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    The code
    Please Login or Register  to view this content.
    checks if the text you type in this case in the Revision? field exists if 0 it does not exist so the next row is checked

    zkTxt is the string that contains all the columns for one row concatenated with a | as separator, that is why with the prd (Product Name) I check the p1 to find the first | and the P2 for the one after that, between p1 and p2 is the data for the product name


    see below some the contents for the first six row, the contents of zktxt for each row
    Please Login or Register  to view this content.

  23. #23
    Forum Contributor
    Join Date
    10-18-2018
    Location
    Amsterdam, Holland
    MS-Off Ver
    2013
    Posts
    104

    Re: Populate listbox

    When i typ 1 i see; 17, 18 and 10.

    I can only continue with 18 cause it's duplicated. It's kinda confusing that i can't typ 17 to filter everything out but 17 for example .

  24. #24
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    Okay I see what you mean, I'll check the code, strange, it worked before.
    Will get back to you

  25. #25
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    Okay, I made it too sophisticated, I removed some code and it works now, the only thing is that you can keep on typing even if no results aer found, I wanted to avoid that.
    Have something else in mind but this just so it works for you now
    Attached Files Attached Files

  26. #26
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    Okay this way is better and it works (tested)
    Attached Files Attached Files

  27. #27
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    I like dotchiejack's sample a lot, I took the liberty to make some minor modifications.
    No extra functionality, just modified things.

  28. #28
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Populate listbox

    It's complete now if you do not select anything you can type anything in the search box

  29. #29
    Valued Forum Contributor dotchiejack's Avatar
    Join Date
    05-21-2015
    Location
    Antwerp,Belgium
    MS-Off Ver
    2016
    Posts
    507

    Re: Populate listbox

    @Keebellah,
    Nice

+ 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. Replies: 7
    Last Post: 01-29-2017, 03:22 PM
  2. Populate (ca 200) Txtboxes depending on listbox selection, live-filter for listbox &1 more
    By InternInNeed in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-14-2016, 09:56 AM
  3. [SOLVED] How do I populate a listbox with a list excluding values found in another listbox?
    By Hokiefan00 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-27-2014, 01:47 PM
  4. VBA - Populate Listbox based on value selected in another ListBox (On Userform)
    By raaboo in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-12-2012, 11:18 AM
  5. Populate userform listbox based on value selected in another listbox
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-07-2012, 05:16 PM
  6. Populate ListBox Based On Item Chosen In Another ListBox
    By davemojo82 in forum Excel General
    Replies: 1
    Last Post: 08-04-2009, 08:39 AM
  7. populate listbox based on selection in previous listbox
    By sarahng86 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-28-2007, 10:55 PM

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