Changing ListBox population with VBA

[Zerb]

If I have a ListBox that is populated by cells A1:A5 and I want to change that ListBox to only show cells A1:A3 based on other VBA code (a selection made from a separate ListBox), what is the best way for me to do that? Is removing the items in the ListBox then adding them back when I need them the best method? Or dynamically changing the range within the ListBox? If I were to change the cell range from A1:A5 to A1:A3 I wouldn’t want two blank rows to just hang out within the ListBox. Thank you.

 

[6StringJazzer]

How do you initially populate the ListBox?

If you set the ListFillRange you can just change it. I have never done this before in code but I would imagine that the cleanest method would be to first call ListBox1.Clear then update ListFillRange.

If you call ListBox1.Add in a loop to add values, then call ListBox1.Clear and run another loop to populate with different values.

Neither method will cause you to have two blank rows.

 

[Zerb]

How do you initially populate the ListBox?

If you set the ListFillRange you can just change it. I have never done this before in code but I would imagine that the cleanest method would be to first call ListBox1.Clear then update ListFillRange.

If you call ListBox1.Add in a loop to add values, then call ListBox1.Clear and run another loop to populate with different values.

Neither method will cause you to have two blank rows.

I initially populate the ListBox using the list fill range in the properties, I am using an Active X ListBox but I can change that to a Form Control if need be, I just liked the look of the Active X option better.

I will try your method, the Clear is most likely my issue because I was able to populate the ListBox with VBA but it kept just adding to what was previously in the ListBox. So if I went from A1:A5 then to A1:A3 it would just add the A1:A3 options within the ListBox below the already seen A1:A5 options.

 

[My Aswer Is This]

Lets assume you want to add all the values in column A of the active worksheet to your listbox:
Try this:

Private Sub CommandButton1_Click()
'Modified  1/2/2022  3:25:49 PM  EST
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ListBox1.Clear
ListBox1.List = Range("A1:A" & Lastrow).Value
End Sub

 

[6StringJazzer]

If your VBA was using ListBox1.Add, then yes you would have to .Clear first otherwise you will just be adding items on top of what's there.

 

[Zerb]

So I attempted to try a few things here and nothing seemed to work, not exactly sure what I could be doing wrong here but I have screenshots to show the issues.

I don't need to .Clear the ListBox entirely, although I tried to and it didn't seem to work, if I could just .RemoveItem and then I can .AddItem back at a different time that would be swell.

 

[My Aswer Is This]

You just said:
I don't need to .Clear the ListBox entirely, although I tried to and it didn't seem to work
I showed in my post how to clear listbox1.clear
Remove any values you have in ListFillRange

 

[6StringJazzer]

For showing us code I would suggest pasting your code directly into your post, highlight it, then click the "VBA" button in the text editor to format it as code. That will be so much easier for you and us vs. taking a screen shot and showing a picture. See post #4 above for how that looks.

 

[Zerb]

For showing us code I would suggest pasting your code directly into your post, highlight it, then click the "VBA" button in the text editor to format it as code. That will be so much easier for you and us vs. taking a screen shot and showing a picture. See post #4 above for how that looks.

I will do that in the morning. Thank you.

You just said:
I don't need to .Clear the ListBox entirely, although I tried to and it didn't seem to work
I showed in my post how to clear listbox1.clear
Remove any values you have in ListFillRange

I will use your VBA code in the morning. I attempted to follow a different way that I thought would work reading some other things online, obviously incorrect.

Is there a reason my .RemoveItem code also does not work? As I said moving further into what I need to create here the .Clear option isn’t exactly what I need to do, clear the entire ListBox then repopulate with some of but not the entire original ListFillRange. Instead of clearing the entire ListBox I can just remove 1 or 2 items and achieve the same thing I’m looking to do.

 

[My Aswer Is This]

If you use:
ListFillRange

You cannot remove or add items.

If you use my code to load the listbox you can add and remove items.
But then do you know how to do that? Remove what item and add what item?