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.
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.
Option Explicit Private Sub ListBox1_GotFocus() Dim c As Range, lastrow As Long lastrow = Cells(Rows.Count, "G").End(xlUp).Row Application.ScreenUpdating = False With Me.ListBox1 .ListFillRange = "" .Clear For Each c In Me.Range("G1:G" & lastrow - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible) If c.EntireRow.Hidden = False Then .AddItem c.Value End If Next c End With Application.ScreenUpdating = True End Sub
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.
I'd prefer the method 'list' to populate listboxes/comboboxes:
sub snb() for each cl in sheets(1).columns(7).specialcells(xlcelltypevisible) c01=c01 & "|"& cl.value next Listbox1.list=split(mid(c01,2),"|") End Sub
Last edited by snb; 07-05-2011 at 02:43 AM.
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.
Doesn't make any difference to the code, only to the place to store/activate the code.
Originally Posted by snb
I can't get it working.
Where should I store it?
Principle is the same, but you did say your listbox was on a userform.
Everyone who confuses correlation and causation ends up dead.
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
The code is basically the same. I would not recommend putting activex controls on worksheets though.
If it is the same code why can I not get it working?
What is the problem with placing them on a worksheet?
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.
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]
You need to remove the ListFillRange from your listbox, then the code will work.
I did but now it's empty ?
You need to run the code to populate the 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.
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?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!
Private Sub UserForm_Initialize() Dim x As Long Dim k As Long Dim j As Long x = Sheets("INPUT WV").Range("D10:M540").Rows.Count ListBox1.List = Sheets("INPUT WV").Range("D10:M540").Value For k = 1 To x 'rows For j = 1 To 10 'columns ListBox1.List(k - 1, j - 1) = VBA.Format(ListBox1.List(k - 1, j - 1), "#,##0") Next Next For j = 1 To 10 Me.Controls("TextBox" & j).Text = "" Next j End Sub
For example:
If speed is your goal:
Private Sub CommandButton1_Click() Cells(1, 60).CurrentRegion.ClearContents Cells(1).CurrentRegion.Offset(1).Copy Cells(1, 60) ListBox1.List = Cells(1, 60).CurrentRegion.Value End Sub
Last edited by snb; 07-08-2011 at 09:14 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks