Get values from a listbox on a sheet

Question

I have a listbox named ListBox1 on Sheet1 of an Excel workbook   Every time the user selects one of the items in the list  I need to copy its name to a variable named strLB    So  if I have Value1  Value2  Value3  Value4 and the user selects Value1 and Value3  I need my strLB to come out as Value1 Value3   I tried doing that post hoc with   For i   1 To ActiveSheet ListBoxes  ListBox1   ListCount     If ActiveSheet ListBoxes  ListBox1   Selected i  Then strLB   strLB  amp  etc etc  Next i   But this is very slow  I have 15k values in my listbox   This is why I need to record the selection in real time and not in a cycle  after the user is done inputting   I m going to also need a way to check if the user removed any of the previous selection

User · Answer

To get the value of the selected item of a listbox then use the following.

For Single Column ListBox: ListBox1.List(ListBox1.ListIndex)

For Multi Column ListBox: ListBox1.Column(column_number, ListBox1.ListIndex)

This avoids looping and is extremely more efficient.

User · Answer

Take selected value   worksheet name   ordls form control list box name   DEPDB1  selectvalue   ordls Shapes  DEPDB1   ControlFormat List ordls Shapes  DEPDB1   ControlFormat Value

User · Answer

Unfortunately for MSForms list box looping through the list items and checking their Selected property is the only way  However  here is an alternative  I am storing removing the selected item in a variable  you can do this in some remote cell and keep track of it     Dim StrSelection As String  Private Sub ListBox1 Change       If ListBox1 Selected ListBox1 ListIndex  Then         If StrSelection      Then             StrSelection   ListBox1 List ListBox1 ListIndex          Else             StrSelection   StrSelection  amp       amp  ListBox1 List ListBox1 ListIndex          End If     Else         StrSelection   Replace StrSelection       amp  ListBox1 List ListBox1 ListIndex           End If End Sub

User · Answer

The accepted answer doesn t cut it because if a user de-selects a row the list is not updated accordingly   Here is what I suggest instead   Private Sub CommandButton2 Click       Dim lItem As Long      For lItem   0 To ListBox1 ListCount - 1         If ListBox1 Selected lItem    True Then             MsgBox ListBox1 List lItem           End If     Next End Sub   Courtesy of http   www ozgrid com VBA multi-select-listbox htm

[excel] Get values from a listbox on a sheet

Examples related to excel

Examples related to vba

Examples related to listbox