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.
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
M | T | W | T | F | S | S |
---|---|---|---|---|---|---|
1 | 2 | 3 | ||||
4 | 5 | 6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 | 16 | 17 |
18 | 19 | 20 | 21 | 22 | 23 | 24 |
25 | 26 | 27 | 28 | 29 | 30 |
Recent Posts
- VBA – Import CSV file
- VBA – Get name of file without extension
- UserForm Listbox – Populate list and extract selected items
- VBA – Retrieve Last Row From More Than One Column
- VBA – Check Extension of File
- VBA – Delete PivotTables
- VBA – Add New WorkSheet After The Last Worksheet
- VBA – Toggle Between Open Excel Files
- VBA – Looping through all files in a folder
- VBA – Create and add items to dynamic arrays
- VBA – Loop through arrays
- Excel formula – Miscellaneous
- VBA – Delete all files in a folder
- VBA – Loop through sheets
- VBA – Define PageSetup (PaperSize, Orientation, etc.) and Print Excel Sheet