+ Reply to Thread
Results 1 to 44 of 44

Fill Listbox with multiple collumns based on textbox value

  1. #1
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59

    Fill Listbox with multiple collumns based on textbox value

    Hello,
    I'm new to the excel program and this forum but i'm developing a program on excel and i am trying to fill a listbox with multiple columns based on a text box value it works with a single column but i want to fill it with all columns on the worksheet.
    Forgot to mention that my list contains 24 columns.

    Heres's the code i found on a forum:

    Please Login or Register  to view this content.

    Thanks in advance and i'm sorry if it's a double post.
    Last edited by alansidman; 06-23-2018 at 09:02 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Fill Listbox with multiple collumns based on textbox value

    Code Tags Added
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    Hi & welcome to the board.
    How about
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    I still wasn’t’able to test the code, but I would like to know if I need to make some changes on it with less collums?

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    To change the number of columns change the value in red
    Please Login or Register  to view this content.
    I also noticed that I had forgotten to change one of the values, so this code not the one post#2

  6. #6
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    It gives me a error on the Me.listbox1.list = Nary
    Could not set the list property. Invalid property array index
    Edit: I was using it on the userform activate, but when I bind this code to a button it juste adds me the values on one column and not the rest
    Last edited by dark_prince69; 06-23-2018 at 11:49 AM.

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    That sounds as though there is nothing in col A that matches TextBox19

  8. #8
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    Quote Originally Posted by Fluff13 View Post
    That sounds as though there is nothing in col A that matches TextBox19
    Yes it matches and it adds me the value in A but not the value from all the row perhaps I didn’t explain it correctly 😓

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    Can you supply the workbook, as it works for me.

    EDIT
    Also make sure the the columncount property is set to -1
    Last edited by Fluff13; 06-23-2018 at 12:06 PM.

  10. #10
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59

    Re: Fill Listbox with multiple collumns based on textbox value

    here you go


    im unable to upload my file for now
    where do i set the column proprety? on the listbox?
    Last edited by dark_prince69; 06-23-2018 at 12:19 PM.

  11. #11
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    Quote Originally Posted by dark_prince69 View Post
    here you go


    im unable to upload my file for now
    where do i set the column proprety? on the listbox?
    Great it works now! 😊

    It still have some questions, can I still hide some of the Collumns on the ListBox? I want to add them to the list but hide some of them so it shows the hidden values on captions, with the column width I maybe be able to do it? And last but not list of the text box is empty it gives me an error since there’s no data on it, on the userform activate what do I need to change to show all data from sheet?

    You’re awesome 😎 thanks a lot man!

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    Try
    Please Login or Register  to view this content.
    Adjusting the ColumnWidths values as needed & add the rest in the same way.
    Also if you make this change to the previous code
    Please Login or Register  to view this content.
    It will prevent an error if there is nothing to display

  13. #13
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    It no longer gives an error if it’s empty but I is not found it gives the same error and on the list it doesn’t find the last row and it adds all empty cells 😓

  14. #14
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    Try this change in case textbox19 value not found
    Please Login or Register  to view this content.
    Re the last row not showing. Do you have data in col A for every row?
    Which empty cells is being added?

  15. #15
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    Cool it’s working now for the list, just one more thing 😅 the headers for the list are showing as a item on the list and not header, for the last row i didn’t explained correctly, the last row is show but the empty cells are also on the list 😓

  16. #16
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    This will get rid of the header row appearing
    Please Login or Register  to view this content.
    You can't use ColumnHeaders when populating a listbox in this way.

  17. #17
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    It doesn’t work

  18. #18
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    In what way?

  19. #19
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    Quote Originally Posted by Fluff13 View Post
    In what way?

    Yes it yellows this part
    Me.ListBox1.List = Intersect(.UsedRange.Offset(1).Resize(.UsedRange.Rows.count - 1), .Range("A:X")).Value

  20. #20
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    What was the error message?

  21. #21
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    Quote Originally Posted by Fluff13 View Post
    What was the error message?
    Application defined or object-defined error

  22. #22
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    The only way I can get that error is if sheet Suivi2 is either blank, or has only 1 row of data.

  23. #23
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59

    Re: Fill Listbox with multiple collumns based on textbox value

    its not the case, but heres my file bellow
    https://uptobox.com/ktc4aryqghvr

    Its the code on Userform "SuiviRep"

  24. #24
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    Ok, firstly you need to change the button code to
    Please Login or Register  to view this content.
    Secondly. The reason you are getting the error is that the usedrange is from A1 to AG65536.
    You need to goto the last row of data in that sheet & delete every row from there to the bottom of your sheet. & do the same with the columns.
    Save the workbook & everything should then work.
    If you do that for all the sheets the file size will drop to ~19Meg

  25. #25
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    Yes it’s working now, but I have a small problem how can I add another row because this sheet will be constantly adding new values since the rows are deleted?

  26. #26
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    The ranges are all dynamic, so that will be taken care of automatically.

  27. #27
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59

    Re: Fill Listbox with multiple collumns based on textbox value

    Yes i know that, but when I use this code it doesn’t work because all empty rows are deleted and if I use a macro to create one it’s creates one before the last row and not after.

    Sheets(“suivi”).range(“a” & rows.count).end(xlup).offset(1).value = format(now, “dd.mm.yyyy”)
    Before this the code worked..
    Last edited by dark_prince69; 06-23-2018 at 05:20 PM.

  28. #28
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    I don't understand what you're saying.
    And that line of code appears nowhere in the file you supplied

  29. #29
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59

    Re: Fill Listbox with multiple collumns based on textbox value

    i found how to create a new row but i want write values on the last empty row and that code worked before when the empty rows where visiblep

    Ps: nevermind i was using the wrong sheet it was sheet(“suivi2”) instead on suivi..
    Last edited by dark_prince69; 06-23-2018 at 05:49 PM.

  30. #30
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    Quote Originally Posted by Fluff13 View Post
    I don't understand what you're saying.
    And that line of code appears nowhere in the file you supplied
    Sorry to ask this but if I want to filter the ListBox as i type in texbox how can I do it?

  31. #31
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59

    Re: Fill Listbox with multiple collumns based on textbox value

    Sorry to bother you, how can i change the code so the value i need to find contains the value on the textbox and not need to match.
    for example that shows me the list with all the values that begin or contain the number 3.

  32. #32
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    I'm afraid I've no idea.

  33. #33
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    Quote Originally Posted by Fluff13 View Post
    I'm afraid I've no idea.
    Thanks you very much anyway for everything you’ve been a big help for me.

    Does anyone knows how to?

  34. #34
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    Quote Originally Posted by Fluff13 View Post
    I'm afraid I've no idea.
    I’ve encountered another problem perphaps you can still help me, If i need to change the search column in the code what’s the code I need to change? Until column 4 I was able to do it but past it it gives me an error
    Subscript out of range and yellows this code
    Nary(k,j) = arrLidt(I,j)

  35. #35
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    You need to change the parts in red on these 2 lines
    Please Login or Register  to view this content.

  36. #36
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    If it try to find the same row but under another column it shows with no problem

  37. #37
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    Quote Originally Posted by Fluff13 View Post
    You need to change the parts in red on these 2 lines
    Please Login or Register  to view this content.
    That’s what I dit but only column 5 that gives me this error..

  38. #38
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59

    Re: Fill Listbox with multiple collumns based on textbox value


  39. #39
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Fill Listbox with multiple collumns based on textbox value

    It's because you have names like Manuel and Emmanuel
    The countif will only count the number of times Manuel exists but the Instr will return both as manuel is in emmanuel

  40. #40
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59
    Quote Originally Posted by Fluff13 View Post
    It's because you have names like Manuel and Emmanuel
    The countif will only count the number of times Manuel exists but the Instr will return both as manuel is in emmanuel
    Oh...and what’s the seems to be the solution??

  41. #41
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59

    Re: Fill Listbox with multiple collumns based on textbox value

    Sorry to bother you, there's a solution for this?

    I have another problem, I had this code to modify the cells on the sheets trough the listbox but since I no longer use the rowsource like before how can I modify this code bellow?


    Please Login or Register  to view this content.

  42. #42
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59

    Re: Fill Listbox with multiple collumns based on textbox value

    I still haven't found a solution to this problem :-( can anyone help me?

  43. #43
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59

    Re: Fill Listbox with multiple collumns based on textbox value

    so i've found this code on the internet but i have a bug as you can see when i type the first number the first item shows me 2 times
    Please Login or Register  to view this content.
    Attached Images Attached Images
    Last edited by dark_prince69; 08-24-2018 at 09:00 PM.

  44. #44
    Registered User
    Join Date
    06-22-2018
    Location
    suisse
    MS-Off Ver
    office 2016
    Posts
    59

    Re: Fill Listbox with multiple collumns based on textbox value

    So I found a way to prevent this problem, but now I want to know if it’s possible to delete items from the list box with this code?

+ 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. VBA - Adding Search to Listbox based on Textbox value
    By nimesh29 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-09-2014, 06:47 PM
  2. How is it possible to fill in data from a textbox into a selected row in a listbox
    By vbastarter14 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-02-2014, 02:23 PM
  3. VBA listbox based on value in textbox
    By excelvraag in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-16-2012, 11:11 AM
  4. Replies: 12
    Last Post: 08-28-2012, 07:09 AM
  5. Replies: 3
    Last Post: 08-10-2011, 02:02 PM
  6. fill a listbox from textbox of other tab of userform in excel vba
    By kmlprtsngh in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-22-2010, 03:36 AM
  7. update data based on listbox & textbox
    By SarahPintal in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-13-2010, 02:37 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