+ Reply to Thread
Results 1 to 29 of 29

Show only visible cells/rows in Listbox

  1. #1
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Show only visible cells/rows in Listbox

    Hi all,

    Is there a way to populate a Listbox with only the visible cells of a range.

    The problem is that when I filter, let's say, column D for "TRUE"s the Listbox remains the same [showing also the hidden data]

    Is there any solution for this.

    I don't need any advanced filters, it only needs to filter columnd D for "TRUE" and show "ALL" data again.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Show only visible cells/rows in Listbox

    I will assume you refer to an Active-X listbox embedded in a worksheet.

    This basic code uses the _GotFocus event of the control to clear the current list items then fill the list by looping the filtered column to add items only from visible cells.

    Adjust range references as required per your worksheet.
    This code must go into the worksheet module and will have to be adapted for use in a user form if that is where you are using the list box.

    Please Login or Register  to view this content.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Show only visible cells/rows in Listbox

    I'd prefer the method 'list' to populate listboxes/comboboxes:

    Please Login or Register  to view this content.
    Last edited by snb; 07-05-2011 at 02:43 AM.



  4. #4
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Show only visible cells/rows in Listbox

    Hello Palmetto and SNB,

    I should have mentioned that the listbox is on a Userform.
    I wasn't aware of an Active-X listbox embedded in a worksheet.

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Show only visible cells/rows in Listbox

    Doesn't make any difference to the code, only to the place to store/activate the code.

  6. #6
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Show only visible cells/rows in Listbox

    Quote Originally Posted by snb View Post
    Doesn't make any difference to the code, only to the place to store/activate the code.

    I can't get it working.
    Where should I store it?

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Show only visible cells/rows in Listbox

    In the userform:

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Show only visible cells/rows in Listbox

    It gives a runtime error on this line:

    Please Login or Register  to view this content.
    But isn't it just possible to populate a Listbox with unhidden rows only.
    So when I filter the actual data on the sheet only those visible are populated in the Listbox?

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Show only visible cells/rows in Listbox

    cfr the attachment
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Show only visible cells/rows in Listbox

    Quote Originally Posted by snb View Post
    cfr the attachment
    I see, Thanks SNB.
    How do I include columns 4,5 and 6 to be shown

  11. #11
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Show only visible cells/rows in Listbox

    SNB,

    Maybe the attached file will explain what I mean
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Show only visible cells/rows in Listbox

    Or perhaps someone else?

  13. #13
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Show only visible cells/rows in Listbox

    Cfr the attachment
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Show only visible cells/rows in Listbox

    Quote Originally Posted by snb View Post
    Cfr the attachment
    Thanks SNB, works great on the Userform.
    How do I get it to work fr the Active-x listox?

  15. #15
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Show only visible cells/rows in Listbox

    Or perhaps Somenone else

  16. #16
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Show only visible cells/rows in Listbox

    Principle is the same, but you did say your listbox was on a userform.
    Remember what the dormouse said
    Feed your head

  17. #17
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Show only visible cells/rows in Listbox

    Quote Originally Posted by romperstomper View Post
    Principle is the same, but you did say your listbox was on a userform.
    Hello Romperstomper, I know, I did say that.
    But at that time I wasn't aware of the possibility to place a listbox on a worksheet.
    Now I rather have it on the worksheet

  18. #18
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Show only visible cells/rows in Listbox

    The code is basically the same. I would not recommend putting activex controls on worksheets though.

  19. #19
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Show only visible cells/rows in Listbox

    If it is the same code why can I not get it working?
    What is the problem with placing them on a worksheet?

  20. #20
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Show only visible cells/rows in Listbox

    Hard to say without seeing what you are actually trying.

    Activex controls on worksheets are prone to odd behaviour (such as changing size) and have been known to corrupt workbooks.

  21. #21
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Show only visible cells/rows in Listbox

    I attached a sample a few posts ago.
    SNB replied on that and got it working for the listbox on the userform , but not for the Listbox on the worksheet. [I suppose he can make it work, but didn't because I asked it to work for the Userform]

    Perhaps you can take a look [see attachment of the last reply of snb]

  22. #22
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Show only visible cells/rows in Listbox

    You need to remove the ListFillRange from your listbox, then the code will work.

  23. #23
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Show only visible cells/rows in Listbox

    I did but now it's empty ?

  24. #24
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Show only visible cells/rows in Listbox

    You need to run the code to populate the listbox.

  25. #25
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Show only visible cells/rows in Listbox

    The code I gave you early on in this thread assumed an active-x list box in the worksheet and the code. Guess you didn't bother to give it a go.

  26. #26
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Show only visible cells/rows in Listbox

    Hello Palmetto,

    I must have overlooked your suggestion, sorry for that
    However I did give it a try, Although it loads instantly [snb's takes a lot of time to load] it seems instable. After I run your code the list keeps on flickering when selecting an item on it.

    Neither of the solutions gave satisfaction, although Snb' works fine on a Listbox in a Userform.
    I'll have to think of something else.

    Thanks however both for helping!

  27. #27
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Show only visible cells/rows in Listbox

    For example:
    Attached Files Attached Files

  28. #28
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Show only visible cells/rows in Listbox

    If speed is your goal:

    Please Login or Register  to view this content.
    Last edited by snb; 07-08-2011 at 09:14 AM.

  29. #29
    Registered User
    Join Date
    10-19-2012
    Location
    Assen
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: Show only visible cells/rows in Listbox

    Quote Originally Posted by Jonathan78 View Post
    Hello Palmetto,

    I must have overlooked your suggestion, sorry for that
    However I did give it a try, Although it loads instantly [snb's takes a lot of time to load] it seems instable. After I run your code the list keeps on flickering when selecting an item on it.

    Neither of the solutions gave satisfaction, although Snb' works fine on a Listbox in a Userform.
    I'll have to think of something else.

    Thanks however both for helping!
    Hi Jonathan, how can i plement it in the code below?

    Please Login or Register  to view this content.

+ 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