get the last selected item in the multiselect box

In order to make ListBox1_Change event returning the last selected list box value, you can use the solution. It can detect the selected value, independent of its position in the list:

  1. Create a Private variable on top of the sheet module where the list box exists (in the declarations area):
Private colS As New Collection
  1. Then copy the next adapted event code:
Private Sub ListBox1_Change()
    Dim i As Long
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            If colS.Count = 0 Then
                colS.Add ListBox1.List(i), ListBox1.List(i)
                If Not itExists(colS, ListBox1.List(i)) Then
                    colS.Add ListBox1.List(i), ListBox1.List(i)
                End If
            End If
            If itExists(colS, ListBox1.List(i)) Then
                colS.Remove ListBox1.List(i): Exit Sub
            End If
        End If
    Next i
    If colS.Count > 0 Then MsgBox colS(colS.Count)
End Sub

If you want it triggering only if the selected value is “Select All”, then replace the last event code line with something like:

 If colS.Count > 0 Then 
   If colS(colS.Count) = "Select All" then
     'do whatever you need in such a case
     'but, if you try selecting all of lines, in order to avoid the event 
     'being triggered again, you should use 'Application.EnableEvents = False`, before selecting and 'Application.EnableEvents = True` after
   End If
 End If

The simplest solution should be the one suggested in the first comment:

 If Listbox1.Selected(1) = True Then
   'do whatever you need
 End If

But, in order to make it working as it should, the line “Select All” should be the second of the list

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top