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.

 

[Zerb]

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?

I think I do, but I haven't gotten that far to see if what I have read will work will work, I am working through your code you posted to load the ListBox first here.

I should have been more clear in stating that I have another sheet within the workbook that I am storing all of my ListBox options. That sheet is named ListBoxOptions and it declared as a Public Dim at the top of my code as this


Public ListBoxOptions As Sheet6

Sub UpdateListBoxes()

Dim LastRow As Long
LastRow = ListboxOptions.Cells(Rows.Count, "B").End(xlUp).Row
ListBox3.List = ListboxOptions.Range("B23:B" & LastRow).Value

End Sub

 

[My Aswer Is This]

If you need more help let us know. And please be specific.
Like sheet names. And how you plan to add and remove items from listbox.
Trying to help when your using code your finding and then wanting pieces of help can be difficult.
Like adding and removing items from a listbox.

 

[Zerb]

First of all, thank you for all of the help. This is where I am at now.

The values that I am looking to populate my ListBox with reside in a Sheet named ListBoxOptions in the Workbook. Cells B2:B5 currently. I like you .xlup option that way if I ever need more options then it will dynamically use them and I won't need to go in and manually change the Range.


Running this gets me an error of "Run-time error '70':

Sub UpdateListBoxes()

Dim LastRow As Long
LastRow = Worksheets("ListBoxOptions").Cells(Rows.Count, "B").End(xlUp).Row
ListBox3.List = Worksheets("ListBoxOptions").Range("B1:B" & LastRow).Value

End Sub

 

[My Aswer Is This]

The code I gave you in Post #4 should do what you want. Just add the proper sheet names.
And change A to B for the range

Your original post said:
A1:A5 to A1:A3

Now your using B

 

[Zerb]

The code I gave you in Post #4 should do what you want. Just add the proper sheet names.
And change A to B for the range

Your original post said:
A1:A5 to A1:A3

Now your using B

Excellent. Yes that worked. The RunTime error was due to me not removing what I had in the Properties of the ListFillRange object. I will move onto .RemoveItem now, thanks!

Sub UpdateListBoxes()

Dim Lastrow As Long

Lastrow = Worksheets("ListBoxOptions").Cells(Rows.Count, "B").End(xlUp).Row
ListBox3.List = Worksheets("ListBoxOptions").Range("B2:B" & Lastrow).Value

End Sub

 

[Zerb]

The code I gave you in Post #4 should do what you want. Just add the proper sheet names.
And change A to B for the range

Your original post said:
A1:A5 to A1:A3

Now your using B

So, last but not least, I changed my mind on .RemoveItem and .AddItem and I just decided to expand upon your code that you laid out for me and depending on which option is chosen from another ListBox I am loading the ListBox that you have helped me with based on different cell ranges, it's working out great thus far.

I have only dealt with single option ListBoxes until this point and I have been able to figure out the VBA required so that I can determine the .ListIndex and the .Value of the selection made within the ListBox. For my next hurdle I would like to have a ListBox that has Multiple Selection options, that I will populate using the Properties because this ListBox doesn't need to be dynamic in its range. But I would like to be able to determine in VBA which options are chosen with the box. How is this accomplished?

 

[My Aswer Is This]

Not sure what your now asking for:

I assume now we are talking about a different ListBox
And you say you want to use the properties and not code to load the Listbox.

And you want multiselection

So what do you want to do?
If you select "Alpha" from Column 2 of listbox2 and "Bravo from column 1 of listbox2 where do you want Alpha and Bravo to be entered.

I think this would require several lines of code and do all these values to be entered in same sheet and same column?

See to help I would need a lot of specific information

 

[Zerb]

Not sure what your now asking for:

I assume now we are talking about a different ListBox
And you say you want to use the properties and not code to load the Listbox.

And you want multiselection

So what do you want to do?
If you select "Alpha" from Column 2 of listbox2 and "Bravo from column 1 of listbox2 where do you want Alpha and Bravo to be entered.

I think this would require several lines of code and do all these values to be entered in same sheet and same column?

See to help I would need a lot of specific information

You are correct. This is a different ListBox that I will just set the ListFillRange by the properties for the ListBox. The Single select ListBoxes that I have been using thus far in my VBA code I am able to return the .ListIndex (integer) as well as the .Value (string), I then use those variables elsewhere in my code to accomplish other tasks.

What I would like to do now is get that same information, the .ListIndex as well as the .Value for a ListBox that can have multiple options selected at a time.

 

[My Aswer Is This]

Here is a example:
The values selected in Listbox1 are added to column "O" of active sheet.
Change ListBox1 to whatever your listbox is named.

Private Sub CommandButton8_Click()
'Modified  1/3/2022  4:42:41 PM  EST
'Add listbox1 values to column O
Dim i As Long
Dim b As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, "O").End(xlUp).Row

For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
        Cells(lastrow + 1, "O") = ListBox1.List(i)
        lastrow = lastrow + 1
        
    End If
Next

For b = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(b) = True Then ListBox1.Selected(b) = False
Next
End Sub