+ Reply to Thread
Results 1 to 11 of 11

Improve speed to populate Listbox based on a Textbox entry

  1. #1
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Improve speed to populate Listbox based on a Textbox entry

    Hello experts,

    Below piece of code works perfectly with the data in most of my sheets (thanks to this forum )

    Currently I'm dealing with a sheet that has wayyyyy more rows to loop through and it takes quite a while to get the desired results in the listbox.

    Is there a way to speed up this process? If so, what do I need to do?
    (I have been reading articles about array's but I'm not familiar with that)

    Here is the current code
    Please Login or Register  to view this content.

    Please Login or Register  to view this content.

  2. #2
    Registered User
    Join Date
    06-17-2012
    Location
    Australia
    MS-Off Ver
    MS Office 365
    Posts
    40

    Re: Improve speed to populate Listbox based on a Textbox entry

    Hi Mama,

    Its not overly clear how your originally populating your listbox data. Easiest way is if your data is stored in an actual worksheet then you can filter the range by your search team, populate an array and finally populate your listbox.

    Below code assumes your data is on sheet(1) and your data is in column A:D

    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Improve speed to populate Listbox based on a Textbox entry

    Try

    Please Login or Register  to view this content.
    Kind regards
    Leo

  4. #4
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Re: Improve speed to populate Listbox based on a Textbox entry

    Hi bradl822,

    Thanks for your reply. You are right about me not being clear with regards to the data (I apologize)
    All data is in a table on 1 sheet in the workbook. The total number of columns is 81 (a lot, i know)

    The listbox gets populated with the first 9 columns of the table and upon entering a value into the textbox all 9 columns will be searched.

    For filtering prior to the actual search of a value in the textbox I used to have optionbuttons on the form (somewhat like you are suggesting)
    That did'nt work on a operational level as the user (helpdesk) mostly doesn't know if the data there looking for is an address, city, country, customernumber, equipmentnumber and so on.

    They get partial information by phone and skipping the optionbuttons by just entering a value in the textbox made the form user-friendly and easier.

    It really worked great with only about 1000 rows but due the huge increase of rows it has gotten slower. That's why I'm trying to speed things up a bit.

    Anyway....thanks for your thoughts on my request.

    Regards,
    Mama

  5. #5
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Re: Improve speed to populate Listbox based on a Textbox entry

    Hi LeoTaxi,

    Thank you for your reply. I'm trying to understand what the code is doing by debugging (and trial and error)

    I think if i could limit the array (arr) to the 9th column (so it would take into account A - I) it would enhance speed a lot as there are now 81 columns in the CurrentRegion.

    Please Login or Register  to view this content.
    Is that possible?

    Regards,
    Mama

  6. #6
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Improve speed to populate Listbox based on a Textbox entry

    Hi Mama,
    For Only 9 Columns

    Please Login or Register  to view this content.
    Kind regards
    Leo

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

    Re: Improve speed to populate Listbox based on a Textbox entry

    If you only need 9 columns, set listbox columncount to 9 and try this.
    Please Login or Register  to view this content.
    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.

  8. #8
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Re: Improve speed to populate Listbox based on a Textbox entry

    @LeoTaxi and Bakerman2

    Thank you, both solutions do speed up the search.

    You make it look so simple!

    Can you tell me how I can adjust my code so that upon selecting a row in the Listbox all (81) textboxes get populated?
    This is the code I have, using the List/Listindex.
    As a result of "limiting" the number of columns to search in the ListBox to 9, populating textboxes will stop after 9 with my code.


    Please Login or Register  to view this content.

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

    Re: Improve speed to populate Listbox based on a Textbox entry

    Number of columns in Listbox = 81, only first 9 will be searched.
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Re: Improve speed to populate Listbox based on a Textbox entry

    That's it! Of course.

    All working smoothly and swift now!

    Thank you so much for your help, time and patience!

    Regards,
    Mama

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

    Re: Improve speed to populate Listbox based on a Textbox entry

    Glad to help and thanks for feedback.

+ 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. [SOLVED] Populate listbox based on a database search using a textbox
    By Dotreena in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 01-25-2018, 06:22 AM
  2. Populate textbox with particular column from multiselect listbox
    By dale.lebar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-24-2017, 01:14 PM
  3. Listbox and Userform to populate textbox - Help!
    By reyreyreyes in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-22-2015, 01:32 PM
  4. [SOLVED] (ActiveX) Populate Listbox With Textbox
    By elsg in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-25-2015, 02:22 PM
  5. [SOLVED] VBA userform search for listbox entry based on Textbox value
    By Hilton1982 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 03-12-2015, 08:29 AM
  6. [SOLVED] Need to populate data in listbox based on textbox value change
    By Naveed Raza in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-20-2014, 07:10 AM
  7. call a cell value from listbox and populate into textbox value
    By molesy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-08-2014, 05:26 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