ListBox Selection Value VBA

Zerb

New Member
Joined
Dec 29, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
What I would like to accomplish is the following...

I have a ListBox object that has 5 options within it to multiselect. In certain cases if an option is selected a certain other option would then disappear from the ListBox, for example if Option 2 is chosen then Option 5 would not be applicable so I would remove it from the list, if Option 2 is unchecked I would then like Option 5 to re-appear within the ListBox. I assume I would be doing this using the .AddItem and .RemoveItem tags in VBA, however I am not sure that the other portion of my code would work out if I do remove and/or add certain options within the ListBox. That portion of code is the following, I want to be able to determine which options are selected within the ListBox via VBA, if Option 2 is the second option and that returns a ( 2 ) in the .Selected tag in VBA and then I were to REMOVE Option 5, if Option 6 was then selected I assume that's now going to return ( 5 ) as .Selected since its the 5th option in the ListBox. If this becomes too complicated I may just do with with CommandButtons and changing visibilities of certian buttons based on the selection of others.


I have the following VBA written which I thought would help me to determine which checkbox(es) are selected within a ListBox object, but I don't seem to be getting the results that I want when using this on a sheet.

Private Sub ListBox1_Click()

Dim x As Integer
With ListBox1
For x = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(x) = True Then
Debug.Print .List(x)
End If
Next x
End With

End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
@Zerb, welcome to MrExcel
for example if Option 2 is chosen then Option 5 would not be applicable so I would remove it from the list, if Option 2 is unchecked I would then like Option 5 to re-appear within the ListBox.
  1. What are the complete requirements? Is it complicated?
  2. I assume there are not duplicate values in the list. So, I'm thinking to refer to the selected option not by the index, but by the value instead. And also use dictionary object to store the original & the changing list.
  3. Are you talking about Listbox on sheet?
 
Upvote 0
@Zerb, welcome to MrExcel

  1. What are the complete requirements? Is it complicated?
  2. I assume there are not duplicate values in the list. So, I'm thinking to refer to the selected option not by the index, but by the value instead. And also use dictionary object to store the original & the changing list.
  3. Are you talking about Listbox on sheet?
1. I wouldn’t say it’s too complicated overall. I would like to have about 4 or 5 ListBoxes with 2 to 5 options a piece in them. If I select a certain option from listbox 1 I would like to remove certain items from other list boxes because the options would be in selectable based on the selection made in listbox 1 for example. Really what I need to understand is how to determine WHAT is selected within each listbox at a given time in vba, then I think I may be able to stumble through the rest on my own.
2. There are no duplicate values within the list.
3. Yes, listbox on sheet. Not on a user form.

Ideally once all of the listbox selections are made I will then have a command button the user would press and that would copy and paste a range of cells from one sheet to another based on the selections made within the list boxes. Again, I have that figured out with vba already. I am stuck on how I determine what I described above. Thanks!
 
Upvote 0
I don't quite understand the requirements.
Let's say listbox1 get value from A1:A5 & listbox2 from B1:B5. The list in Listbox1 is static, while the list in Listbox2 changes by selection in Listbox1.
Say, if option 2 is selected in Listbox1 then remove option 3 from Listbox2
Book1
AB
1aaa
2bbb
3ccc
4ddd
5eee
Sheet1

VBA Code:
Private Sub ListBox1_GotFocus()
    ListBox1.List = Range("A1:A5").Value
End Sub

Private Sub ListBox1_Change()
Dim c As Range, i As Long
With ListBox1
    If .Selected(1) = True Then  'if option 2 is selected
        ListBox2.Clear
        For Each c In Range("B1:B5")
            i = i + 1
            If i <> 3 Then ListBox2.AddItem c.Value 'add item except no 3
        Next
    Else
        ListBox2.List = Range("B1:B5").Value
    End If
End With

End Sub
 
Upvote 0
Solution
Yes exactly!

In my case ListBox1 would have 2 options, Listbox2 would have 4, but one of them would be removed depending upon the selection that was made in ListBox1. I have been doing this with an ActiveX ListBox, is there a reason that I should or shouldn’t be using that versus the Form Controls ListBox? The former seemed to have more options that I could control, which is the only reason I started with it.
 
Upvote 0
I don't quite understand the requirements.
Let's say listbox1 get value from A1:A5 & listbox2 from B1:B5. The list in Listbox1 is static, while the list in Listbox2 changes by selection in Listbox1.
Say, if option 2 is selected in Listbox1 then remove option 3 from Listbox2
Book1
AB
1aaa
2bbb
3ccc
4ddd
5eee
Sheet1

VBA Code:
Private Sub ListBox1_GotFocus()
    ListBox1.List = Range("A1:A5").Value
End Sub

Private Sub ListBox1_Change()
Dim c As Range, i As Long
With ListBox1
    If .Selected(1) = True Then  'if option 2 is selected
        ListBox2.Clear
        For Each c In Range("B1:B5")
            i = i + 1
            If i <> 3 Then ListBox2.AddItem c.Value 'add item except no 3
        Next
    Else
        ListBox2.List = Range("B1:B5").Value
    End If
End With

End Sub
I appreciate the help here and the VBA you posted, however I’m not sure if it’s exactly what I need to do.

What would be the VBA for me to know what is selected in the ListBox? For the ListBoxes that are only single selection at a time I think I can use the .Selected tag in VBA to tell me when option is selected at any given time right? But for the multi option ListBoxes I get hung up there. For example if I have a ListBox that has 5 possible options and I can select as many as I want to, how could I know if options 2,3 and 5 are currently selected
 
Upvote 0
I don't quite understand the requirements.
Let's say listbox1 get value from A1:A5 & listbox2 from B1:B5. The list in Listbox1 is static, while the list in Listbox2 changes by selection in Listbox1.
Say, if option 2 is selected in Listbox1 then remove option 3 from Listbox2
Book1
AB
1aaa
2bbb
3ccc
4ddd
5eee
Sheet1

VBA Code:
Private Sub ListBox1_GotFocus()
    ListBox1.List = Range("A1:A5").Value
End Sub

Private Sub ListBox1_Change()
Dim c As Range, i As Long
With ListBox1
    If .Selected(1) = True Then  'if option 2 is selected
        ListBox2.Clear
        For Each c In Range("B1:B5")
            i = i + 1
            If i <> 3 Then ListBox2.AddItem c.Value 'add item except no 3
        Next
    Else
        ListBox2.List = Range("B1:B5").Value
    End If
End With

End Sub
To add even more to this…

Even if I were to know what’s selected within the ListBox I’m not sure if that would suffice. Say I have two ListBoxes like your example. If option 2 was selected within ListBox2 then it would return a certain value in the code based on the posting it was in within the ListBox. But if ListBox1 would remove say option 1 from ListBox2, then things I assume would get all messed up because selecting Option 2 now in ListBox2 would be a different name. Is there a way for me to get the string from each selected ListBox item in VBA? That may be my best option.

I’ll try and make screenshots tomorrow am to further explain here.
 
Upvote 0
Is there a way for me to get the string from each selected ListBox item in VBA? That may be my best option.
Sorry for the late reply.
Probably something like this:
VBA Code:
Private Sub ListBox1_Change()
Dim x As Long

With ListBox1
    For x = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(x) = True Then
        If .List(x) = "a" Then
            'do something
        End If
        If .List(x) = "b" Then
            'do something
        End If
    End If
    Next x
End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top