Get the last selected item in multiselect box-CodePudding

I have add a ListBox from Active X Controls in my Excel File and made it a multi select box with checkboxes.

I have also added a selection change event in the VB script against this list box.

Sub lstMultiSelectBox_Change()
  If blnCheck = False Then
     CheckAll
  End If
End Sub

Now what I am struggling to find is that which item was last checked. With this information I want to implement Select All and Un Select All feature in this list box.

CodePudding user response：

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:

Create a Private variable on top of the sheet module where the list box exists (in the declarations area):

Private colS As New Collection

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)
            Else
                If Not itExists(colS, ListBox1.List(i)) Then
                    colS.Add ListBox1.List(i), ListBox1.List(i)
                End If
            End If
        Else
            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...