Hello-
I am trying to Filter certain columns in my ListBox based on a selection from ComboBox in UserForm. I found few sample codes online but, they all seem to refer to a single worksheet and since I have multiple worksheets it made it difficult to follow, wanted to see if there is a way to Filter just the Listbox columns?
1) I have listbox that is populating from multiple worksheets.
2) Trying to filter out the list using Combobox2 and Combobox3. Selection in combobox2 triggers Rowsource list in Combobox3 and which I like to use to filter Listbox columns by using the Command Button. Combobox3 has multiple Rowsource based on the selection in Combobox2.
You would be better off putting the data into a scratch worksheet. You can then use AutoFilter to filter your data or Advanced filter or such. Obviously, that is all done via a macro.
Kenneth- I see what you are saying, I already have the data combined on a separate sheet and I could use that sheet to pull the Filtered list.
Do you see any issue with updating the entry and sending it to correct sheet? I am guessing not since I am only using the combined sheet to populate the Listbox.
Currently, I am able to send data to a selected sheet and update those entries on those sheets by selecting the entry on the listbox but, Once the list gets longer it will be difficult to find things or just Pain in the neck! That's where filtered list come in to narrow down the list.
Now just need to put filter command together. Do you know any good examples?
I may be out of pocket for some time or I would show you. Startoff recording a macro. Sheet1.UsedRange.SpecialCells(xlTypeVisible) or such will probably be used. If I get back and you have no help, I will show you.
Thank you.
In the mean time, I'll give it a go and see if I could get this to work.
-OK...looked into this further and problem might be I am already populating the list box from multiple worksheets and I am asking it to replace current listbox info with Filtered list from combined sheet.
1.) I would need to clear the Listbox1 and replace it with new Filtered list with Command Button.
2.) Then select an item to edit and update
3.) Then clear the filter list and reload full list from all the worksheets with updated information with click of UPDATE command button.
Work up a simple obfuscated example and attach it if you like.
If you are going to get data rows from numerous sheets, pick a row in listbox to edit, and then port the change back to the worksheet it came from, I suggest this. If you have 4 columns of data. Make your scratch sheet have 5. The last column would identify the sheet, and row, it came from.
Since one can determine which row in list box was selected for edit, that row can be edited directly. I guess you would get each column in the row and put the value in a textbox to edit. There should be no need to write back to the sheet it came from and then redo the scratch sheet and listbox contents.
Thank you for your help with this.
So far I have working:
1- Sending data to different sheets.
2- Populating Listbox from different sheets.
3- Selecting from listbox and updating entry in the correct sheet
4- Combine data to the scratch sheet.
Now how do I go about filtering the data and show that in the listbox. I have 2 combobox that would be used to filter the data to narrow the list for easy editing and not scroll through 100s of entry to find and edit.
Possible solution:
-Send the data as I am sending currently to different sheets,
-Collect the data in "Combine Data" sheet from all other sheets,
-Then use "Combine Data" sheet to populate My Listbox and from here I am able to an entry in the listbox and edit/ update as required.
Possible Issue:
-I would need to call to combine with both Send and Update button to refresh my Listbox with the latest info. and that could possibly slow the process as the data entry starts increasing?!?
I like that you used a dynamic named range for your fill lists. Of course one can create the lists from the data if upkeep is too much trouble.
The Columns and Add methods will be slower than more elaborate coded array methods if you have a large dataset to add. Below 10,000 records/rows of data to fill, it should be fine. I guess you can change to the array method if it gets too slow.
For easy coding, I would recommend that the first column heading start at A1 for the DIV sheets. That makes it easier using UsedRange and CurrentRegion.
It will probably be later tonight or tomorrow before I can work on the AutoFilter example. Another time saver might be to not make a scratch sheet and use Autofilter for each DIV sheet.
So, we want to keep the big picture in mind when coding. Howsoever, a solution today though seemingly inefficient but works, is better than fast or/or smaller code some obscure time in the future.
Kenneth- If it's possible to do this without the scratch sheet that would be better!
Regarding the column heading starting at A1, I was planning on having some general information at top of the header. But, I could take that out.
I have not forgotten about you. I have been dealing with some family health issues.
This should give you an idea of what I was talking about. See UserForm1's Initialize event. I have not added the Filter part yet but a similar concept will be used. The 2d sort did not work but there are some more that I wanted to try. Note how column 5 in Listbox1 shows the full address. With this, we know where that row's first column A cell came from. We can set the column width to 0 in the production version to hide.
As I said, while seemingly more work, for a large dataset, it will be worth it in the end.
Hi Kenneth-
No worries and hope all is well with the family.
I just opened the file and going through it and it looks a lot cleaner than the mess of a code I had going on.
I will need to study this to figure out how the additional modules come into play.
This project in its current form is a whole lot better than what I am doing now, entering data into individual sheets.
It would recommend adding a sheet event to remove a row if it is changed to be blank. Until then, I have added a sheet sort as you did to remove the blank row. I also found a bubblesort to sort the final array. If it gets too slow, you can always use a scratch sheet in that workbook or a scratch workbook to add the final array, worksheet sort, refill the array, and then scratch cleanup. That is easily added later.
I should get time to work on the filter part. With this first part done, I don't see the filter part taking much time. Then you can easily do your other parts using Listbox1's column 5.
Cool...
Since this was a sample file I didn't go overboard with the form, I should be able to add additional controls on the userform and columns in the listbox? I'll have close to 16 controls/ columns.
I am getting an error with Update button?
Goal with Userform is to create an entry soon as I get something in and fill in much of the information I have and then update additional controls (textboxes) once I have more info. at a later time and Close out the entry. By selecting the entry in the Listbox, all the controls get filled and you are able to update or fill in missing information and send it back with Update button.
Hope this is clear!
I did get an update to work but, couldn't figure out the Search/ Filter.
I decided to use a scratch sheet but still used my array methods. The methods are similar to what I showed you in your other thread.
The Update routine should be reviewed to make sure that you understand it. If you show the DIV sheet for a listbox selection but then let the user change that sheet, you may or may not want that changed. If changed, it may have to be added to that sheet rather than an Update to where the listbox entry shows it.
Kenneth-
I didn't get a chance to review the file yet I'll be doing it soon but, you are Correct on the Update routine, it was done to update the entry to that sheet, not to the Listbox. I run userform initialize to refresh the listbox new updated information.
Kenneth-
This is exactly what I am looking to do.
Thank you very much for your help with this.
Quick question: How are you controlling the column widths in listbox (Just found it didn't know you could do that...nice)and I could have more than 10 columns with this code?
Reply With Quote
I see what you are saying, I already have the data combined on a separate sheet and I could use that sheet to pull the Filtered list. 
