Trying to use RowSource in a UserForm ListBox, referencing a sheet name by a variable

Hi.

PHP Code: 

'First assign a variable the data range (without the titles):

With Sheets(1)
  Set Rng = .Range("A2:E" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With

'And then assign the range to the listbox:

ListBox1.RowSource = Rng.Address(External:=True) 


It was a great pleasure to help you, thanks for the +rep and until the next help.

Or if you want to keep it simple:

Please Login or Register  to view this content.

Change references as required.
You don't need to give me a star as all I am trying to do is help people without requesting recognition.

@jolivanes & @JEC - sound advice.

Originally Posted by Spielberg

Thanks for that, I had actually tried that before RowSource because I thought it would be easier to use .Clear.

Then I realized I really didn't need to clear it.

But thanks!

It is much smarter to use RowSource than List because it allows you to see the same format in the listBox as in the cells.

And the "formal" equivalent to:
ListBox1.Clear

is:
ListBox1.RowSource = ""

This site has a good explanation on these issues, some confirming, others contradicting.

https://excelmacromastery.com/vba-listbox/

And another site that has more information that will save many an headache.

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

Golden rule, never refresh RowSource if you have been swopping or activating other sheets (it gets lost and some surprise will haunt you).

Hello Artik.
The technique you use to add rows to a listObject invariably leads to a well-known bug.

But the solution is really simple:

PHP Code: 

ListBox1.RowSource = "" 


So I'm going to show you how to add or delete rows.

Originally Posted by Marc L

In this case better is to use a ListView rather than a ListBox …

I agree, Marc, but with one caveat: The number of cells with data! as this last detail is what makes the ListView inefficient.

As a ListView can work with ADO SQL I can't see why it could be less efficient than a ListBox …

As using an UserForm ListBox is most of the time useless 'cause less features than directly under Excel
so the same with a ListView, a DataGrid, whatever …

So the use of any ListBox or any alternative must be devoluted for small ancillary tasks but not for main Excel purposes.
From hundreds & hundreds UserForm I've seen I found very few (far less than ten) really necessary …
The classic no sense : « I wan't to filter my data within an UserForm » so why not directly use the Excel filter or advanced filter ?!

Re: "is most of the time useless"

I think that the vast majority of people seeking help on these sites are not professionals like you, Marc L and beyond Excel, are. Some, or maybe most, of your suggestions are fantastic.
However, in my opinion, the vast majority again I think, are learning with baby steps and will not be able to fix or change these contributions to suit their needs. Of course, some people don't care as long as it does what they asked for.
To me a perfect example is looping and non looping suggestions. Most of us are aware of the fact that looping generally is slower but with todays equipment for most users not noticeably so, but it is generally easier to follow what is happening with the code as it goes along doing it's thing and thus should be simpler to fix or adjust.

Don't see this as a knock on your contributions because I think they are really good.

Keep up the good work.

Above observation is just my opinion.

Originally Posted by jolivanes

However, in my opinion, the vast majority again I think, are learning with baby steps and will not be able to fix or change these contributions to suit their needs. Of course, some people don't care as long as it does what they asked for.

For those who want (and have the time) to study a bit more, here's a basic listView management model:

- Differentiated format of the columns,
- Sorting by column (with a click on the header of each column),
- Filter by one of the fields, and
- Removal of records.

An interesting exercise is to copy the UserForm and change the listView to a listBox and compare times and performance.

By the way: the "magic" of the model that I attached is not only provided by the listView but is provided by a recordset (Rst): it is it that performs three of the four points mentioned above.

.
(Removed)
.