listbox properties behavior

I'm no Excel guru folks, but this issue I've searched for quite a bit and I'm not sure if this is a typical MS issue or not. There is some VBA here too. I am creating a very simple UF interface, and here is the code I have to throw data to a list box:

Please Login or Register  to view this content.

this works fine and I'm sure there are other ways to do this same thing. However, my question is about the ROWSOURCE, COLUMN WIDTHS and COLUMN HEADS properties . They seem to be very finicky and unreliable, which is something I've noticed with MS's office products. I am simply trying to throw sheet data into the column heads, then add items one by one, as indicated by the code. Most of it works fine. But I'd like to set the properties manually so I don't have to write lines of code. In this day in age, the writing of code is being minimized so much, I assume I can do that too in this instance. Can anyone assist here? I've gotten numerous errors, such as "invalid rowsource property" when typing in ""Sheet1!A1:L1"" into the properties argument. Google says this is fine. thanks.

Adam

See the yellow banner at the top of this page.

thanks for the tip. by the way, was that yellow message at the top pasted just for me? I've never seen that before. was that done by an admin? I would have posted a sample file before, but I suppose my question was a little unclear. forgive me. I'm not a fan of microsoft tools of anykind. =)

this is a xlsm file. the code will run when you open the book.

so you want me to change it AGAIN? sure. I guess. google blocks everything. VBS, even zipped up.

this better?

the yellow message was unclear, ma'am. that's all I'm saying. I have no idea what is up to date, what is hot, what is not, and such. I deal with people all day long that get scammed, but none of them download files. so I had no idea that zip's are used in this manner. scamming and viruses never end.

Listbox column headers only work with the rowsource method of loading.
You have to specify individual column widths - setting width to zero hides the column but the data is there.

Please Login or Register  to view this content.

note between quotation marks
If you load with 'rowsource' method then proceed to filter as you are doing with 'additem' method you will find that everything becomes unstable as the 'rowsource' is constantly reading your worksheet.
Your rowsource should not be contained by quotation marks.

Originally Posted by torachan

Listbox column headers only work with the rowsource method of loading.

what do you mean by "method of loading"?

Originally Posted by torachan

If you load with 'rowsource' method then proceed to filter as you are doing with 'additem' method you will find that everything becomes unstable as the 'rowsource' is constantly reading your worksheet.

do your words refer to hard coding to the rowsource into the box's properties or dynamically setting it code, like in your example code?

Rowsource has many unpleasant features. Use .list to fill a listbox. You can adjust all settings in the design of the form. Also use a table then you always have a dynamic range.

Please Login or Register  to view this content.

Vraag,

considering what it says here: https://docs.microsoft.com/en-us/off....databodyrange

I ran this:

Please Login or Register  to view this content.

and saw it. the real book has hundreds of thousands of rows in it. wouldn't my original method of filtering the data run faster than your code, considering that you are looping everything and skipping one val only? can you tell me what RESIZE(, 10).value does? I couldn't find that in MS docs. thanks.

the site below explains far better than i can the weaknesses of rowsource and advises best methods.
personally i nearly always use tables within any apps I develop, they are very versatile and almost self-maintaining.
Attached your file expanded with the method that I would adopt.

https://www.snb-vba.eu/VBA_Fill_comb...istbox_en.html

thanks much tora! i will get back to you if I run into anything.

Your method of filtering and using .add is the slowest you can use. How fast a loop goes into memory depends on your system. Optionally, you can use the advanced filter to create and use a temporary table.

Please Login or Register  to view this content.

ok you guys. i have plenty to work with. you did a little more work than i was expecting. thanks much. i will return with what I find and do.

This may be the other way around because nothing is deleted.

Please Login or Register  to view this content.

Please Login or Register  to view this content.