[Userform] How to search data in Listbox from textbox with more than 10 columns

Hello,

Recently i got a problem in searching the data from an excel back to the userform listbox with more than 10 columns.

Before that, I used the code with .Additem which can successfully pop outs into the listbox with only 10 columns.

Can anyone help if I need to have 18 columns to search back into the listbox? Thank you very much.

Please Login or Register  to view this content.

You can not load more than 10 columns via AddItem method.
Create an array and use List or Column to load to the listbox.

Can not offer detailed suggestion out of the information you gave.

As @Jindon suggests, use arrays, I have attached One I submitted a few months back on this site.
I use tables for data containment as they are easy to maintain and lend themselves well for combo/listbox loading.
The listbox at the lower right has 31 columns as it reloads the form after selections.
Any queries post back on this site.
torachan.

Thank you jidon and torachan.

Maybe it will be more clear if I upload my file.
my problem is from in the searchform Private sub fill()
the listbox need to show the detail from column S to AJ.

Since 1 incident no. may contain different HAWB no, the listbox may need to show more than 1 row after searching.
now there are 18 columns which cannot use additem.

For Torachan's file, sorry that I cannot find the hidden Sheet 3 and not quite understand.
I'm still a beginner for vba, some codes may not be written in a smart way.
Please help to provide some suggestions. Thank you very much.

Did you write those codes for yourself?

I don't even want to debug it... to hard to follow too many unnecessary module level variables...

@ccmyccmy, Sheet3 is a hidden sheet used for data manipulation, most of time it is blank.
look in properties and make it visible to see what it contains - only hidden for aesthetic reasons.
I will try and help once I have got a feel for the flow.
@jindon, it is not like you to shy away from a challenge
torachan.

Thank you for replying me!

jindon, because your first comment said cannot provide suggestions for the information I gave, so I tried to upload the file see if can help.

torachan, thank you for explaining and I will try to look into it again.


In the meantime, I search online again and see how can make use of the array as jindon's suggestion.
I try to change as following, but the ". Column" part has error.
Do you guys have any suggestions?

Please Login or Register  to view this content.

As I suggested in post#2.
This is only the logic, so you need to adjust it to your need.

Please Login or Register  to view this content.

By the way, I would declare the variables within individual Sub/Function procedure, or pass it if needed.
Declare so many variables as you did is not a good practice.

Could not get the flow !!!
Kept starring at the forest and "could not see the wood for the trees."
I would be doing you a disservice if I tried to make your code work.
So I have stripped it all out and I have started as I would approach it.
The form was far too big for my old steam driven computer so had to cut it down to see what I was doing.
So starting from basics, a step at a time, you have multiple incidents under the same number that you want to filter ????
The code in the search form > type a incident number in the box (adjacent to 'GO' button) > press 'GO'.
If there are matching incidents then all will appear in new listbox (upper rh side)
Select from this choice to populate your form (I have only illustrated this by filling the textbox group starting 'Occured Location')
Look at the code for listbox ('lboSEARCH') you will see it populates the textboxes just using a loop.
If you use the TextBox name number in the same order as your database columns you can populate the form merely by extending the loop size.
This alone will save you hundreds of rows of code even in a relatively simple 'App'
Your next thoughts should now turn to identifying the row ready for 'update' / 'delete'
Time to cool my overheating one brain cell.
torachan.

Hello Jindon, Torachan,

Sorry for the late reply due to the cusy schedule.
Thank you so much for your kind effort in helping me.
Your suggestions inspired me a lot.

I tried to use the 2 dimensional array and it can show the search data as below.
However, there is a bug that for the "1 to 100".
For example, when I a search ID 0002, there will be extra 2 blank rows in front of the search result in the listbox.
I'm still working on this, but it does have a great progress.

If you have any ideas, please kindly share.

Please Login or Register  to view this content.

Post your workbook, your code does not make sense.
torachan

Hello Torachan,

Finally I figured out how to solve the problem!
Here is the code.

It might not be perfect, but it can do what I want.

Thank you again you and jindon for helping me.

Please Login or Register  to view this content.