{"id":140,"date":"2012-11-16T23:26:52","date_gmt":"2012-11-16T22:26:52","guid":{"rendered":"http:\/\/mariaevert.dk\/vba\/?p=140"},"modified":"2012-11-16T23:26:52","modified_gmt":"2012-11-16T22:26:52","slug":"userform-listbox-populate-list-and-extract-selected-items","status":"publish","type":"post","link":"https:\/\/mariaevert.dk\/vba\/?p=140","title":{"rendered":"UserForm Listbox – Populate list and extract selected items"},"content":{"rendered":"

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. <\/p>\n

Step 1: Create a UserForm that contains a Listbox called Listbox1 and a button called CommandButton1. <\/p>\n

Step 2: Create the UserForms’ “Initialize” procedure. <\/p>\n

\"\"<\/a><\/p>\n

Step 3: Populate the ListBox with the letters A to Z: <\/p>\n

\r\nPrivate Sub UserForm_Initialize()\r\n\r\n'Create array\r\nDim AlfabetArray() As String\r\n\r\n'Define content of array (here we have splitted with "|", but you could also use "," or something else.\r\nAlfabetArray = 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", "|")\r\n\r\n'Populate the Listbox with the array of letters\r\nListBox1.List = AlfabetArray\r\n\r\nEnd Sub\r\n<\/pre>\n
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.  <\/p>\n
See the result here<\/a><\/p>\n
Step 5: Extract the selected items from the ListBox and insert them into an array for later use. <\/p>\n
\r\n\r\nPrivate Sub CommandButton1_Click()\r\nDim lItem As Long\r\nDim KolonneNavne() As String    'Array\r\nDim blDimensioned As Boolean    'Is the array dimensioned?\r\nDim lngPosition As Long         'Counting\r\n     \r\nblDimensioned = False\r\n'Loop through all items in the Listbox\r\nFor lItem = 0 To Me.ListBox1.ListCount - 1\r\n         \r\n        If Me.ListBox1.Selected(lItem) Then\r\n        'If the item has been selected we add it to the array\r\n        \r\n            'We check if the array has been dimensioned\r\n            If blDimensioned = True Then\r\n            ReDim Preserve KolonneNavne(0 To UBound(KolonneNavne) + 1) As String\r\n            Else\r\n            ReDim KolonneNavne(0 To 0) As String\r\n            blDimensioned = True 'flag\r\n            End If\r\n            \r\n            'We add the letter to the array\r\n            KolonneNavne(UBound(KolonneNavne)) = Me.ListBox1.List(lItem)\r\n        End If\r\nNext lItem\r\n\r\n\r\n'Loop through array to see which items were selected from the Listbox:\r\n\r\nFor lngPosition = LBound(KolonneNavne) To UBound(KolonneNavne)\r\nMsgBox KolonneNavne(lngPosition)\r\nNext lngPosition\r\n\r\nEnd Sub\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts\/140"}],"collection":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=140"}],"version-history":[{"count":17,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts\/140\/revisions"}],"predecessor-version":[{"id":160,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts\/140\/revisions\/160"}],"wp:attachment":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=140"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=140"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=140"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}