UserForm Listbox – Populate list and extract selected items

So I needed to create a small script that allowed the users to select a number of columns (letters from A to Z) from a list. The purpose was for users to be able to chose which columns in a sheet to print – but that’s not part of this post. We focus on the population of the listbox and the extraction of the selected items of the listbox.

Step 1: Create a UserForm that contains a Listbox called Listbox1 and a button called CommandButton1.

Step 2: Create the UserForms’ “Initialize” procedure.

Step 3: Populate the ListBox with the letters A to Z:

Private Sub UserForm_Initialize()

'Create array
Dim AlfabetArray() As String

'Define content of array (here we have splitted with "|", but you could also use "," or something else.
AlfabetArray = Split("A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|X|Y|Z", "|")

'Populate the Listbox with the array of letters
ListBox1.List = AlfabetArray

End Sub

Step 4: Make sure that the ListBox has its attribute MultiSelect set to ‘1 – fmMultiSelectMulti’ if you want the users to be able to select multiple items with a click on the mouse, or ‘2 – fmMultiSelectExtended’, if the users has to “Ctrl-click” to select multiple items and be able to “scroll-select” multiple items much quicker.

See the result here

Step 5: Extract the selected items from the ListBox and insert them into an array for later use.


Private Sub CommandButton1_Click()
Dim lItem As Long
Dim KolonneNavne() As String    'Array
Dim blDimensioned As Boolean    'Is the array dimensioned?
Dim lngPosition As Long         'Counting
     
blDimensioned = False
'Loop through all items in the Listbox
For lItem = 0 To Me.ListBox1.ListCount - 1
         
        If Me.ListBox1.Selected(lItem) Then
        'If the item has been selected we add it to the array
        
            'We check if the array has been dimensioned
            If blDimensioned = True Then
            ReDim Preserve KolonneNavne(0 To UBound(KolonneNavne) + 1) As String
            Else
            ReDim KolonneNavne(0 To 0) As String
            blDimensioned = True 'flag
            End If
            
            'We add the letter to the array
            KolonneNavne(UBound(KolonneNavne)) = Me.ListBox1.List(lItem)
        End If
Next lItem


'Loop through array to see which items were selected from the Listbox:

For lngPosition = LBound(KolonneNavne) To UBound(KolonneNavne)
MsgBox KolonneNavne(lngPosition)
Next lngPosition

End Sub

Friday, November 16th, 2012 Uncategorized