Friday, 16 March 2018

VBA - MSForms - retrieving a listbox's selected items

Worth laying down some sample code as to how to fill a form's listbox and how to query for the selected items...

We populate a Listbox with a two-dimensional array , like that lifted from a worksheet with lbx.List = vValues. I prefer to communicate the selected items with a Dictionary, because I get a second piece of info, in the code below the row number of the selected item. Then to list the items just use the Dictionary's Items() method. Absolutely no need to redimension arrays, use the Dictionary to cope.

P.S. In code below I have a function CompiledHelpFiles() exported off the shtCompiledHelpFiles sheet which gives a 2d values array. I do this to stop direct access to the worksheet which I think is not good separation of concerns.

P.P.S. Don't forget to set the MultiSelect property on the Listbox to frmMultiSelectExtended.


Private Sub PopulateCompiledHelpFilesListBox()
    Dim vValues As Variant
    vValues = shtCompiledHelpFiles.CompiledHelpFiles
    
    lbxCompiledHelpFiles.List = vValues

End Sub


Private Sub cmdDecompile_Click()

    Dim dicSelected As Scripting.Dictionary
    Set dicSelected = GetListBoxSelectedItems(Me.lbxCompiledHelpFiles)

    Dim vFiles As Variant
    vFiles = dicSelected.Items
    
    

    Dim vFileLoop As Variant
    For Each vFileLoop In vFiles
        '* do work on each file
    
    Next vFileLoop

    

End Sub


Private Function GetListBoxSelectedItems(ByVal lbxBox As MSForms.ListBox) As Scripting.Dictionary
    'returns a dictionary with selected and row number
    Dim dicSelected As Scripting.Dictionary
    Set dicSelected = New Scripting.Dictionary
    
    Dim lRow As Long
    For lRow = 0 To lbxBox.ListCount - 1
        If lbxBox.Selected(lRow) Then
            dicSelected.Add lRow, lbxBox.List(lRow)
        End If
    Next

    Set GetListBoxSelectedItems = dicSelected

End Function


No comments:

Post a Comment