{"id":327,"date":"2019-01-15T21:24:07","date_gmt":"2019-01-15T21:24:07","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=327"},"modified":"2019-01-15T21:27:15","modified_gmt":"2019-01-15T21:27:15","slug":"how-do-you-use-listbox-in-excel","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/","title":{"rendered":"How do you use ListBox in Excel?"},"content":{"rendered":"<h2>What is ListBox control?<\/h2>\n<p>The list box allows you to see multiple items in one view and<br \/>\nallows the user to select one or multiple items in the list.<\/p>\n<p>It helps you to manage a big worksheet, and allows you to put constraints on<br \/>\nyour data lists.<\/p>\n<p>You can design it with the ability to select one item, or multiple item.<br \/>\nIMO, if you are only going to allow selecting 1 item, why not just use a ComboBox?<\/p>\n<p>IMO, a ListBox&#8217;s purpose is to allow selecting multiple items<\/p>\n<p>Here is a ListBox with the multiselect option selected:<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox4-1.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox4-1.png?resize=874%2C858\" alt=\"\" width=\"874\" height=\"858\" class=\"alignleft size-full wp-image-341\" \/><\/a><\/p>\n<h2>How do I use listbox in VBA?<\/h2>\n<p>Here I am using a ListBox to display the values in column A of my worksheet:<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox2.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox2.png?resize=658%2C749\" alt=\"\" width=\"658\" height=\"749\" class=\"alignleft size-full wp-image-332\" \/><\/a><\/p>\n<p>I am loading the UserForm with a command button<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox3.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox3.png?resize=914%2C332\" alt=\"\" width=\"914\" height=\"332\" class=\"alignleft size-full wp-image-333\" \/><\/a><\/p>\n<p>The button is called &#8220;btnOpenForm&#8221;, and I am programming the click event:<\/p>\n<pre class=\"lang:vb decode:true \" >Private Sub btnOpenForm_Click()\r\n    UserForm1.Show\r\nEnd Sub\r\n\r\n<\/pre>\n<p>BTW, here is the code for adding the items from your sheet to your ListBox:<\/p>\n<pre class=\"lang:vb decode:true \" >Private Sub UserForm_Initialize()\r\n    LoadBoxes\r\nEnd Sub\r\n\r\n\r\nSub LoadBoxes()\r\n    Dim intCounter As Integer\r\n        \r\n    'load listbox\r\n    With Me.lstSuppliers\r\n        .Clear\r\n        For intCounter = 1 To 132\r\n            .AddItem Sheets(\"Suppliers\").Cells(intCounter, 1).Value\r\n        Next intCounter\r\n    End With\r\nEnd Sub\r\n<\/pre>\n<p><em>{if you can&#8217;t interact with your new button, like click on it, make sure you&#8217;re out of &#8220;Design Mode&#8221;)<\/em><\/p>\n<p>Make the setting on your listbox in the &#8220;MultiSelect&#8221; property: frmMultiSelectExtended (frmMultiSelectMulti allows you to select without the Shift or Ctrl keys, IMO, it&#8217;s not normal for users so don&#8217;t use it)<\/p>\n<p>To select multiple items from your new ListBox, hold down the &#8220;CTRL&#8221; key while you left click.  With consecutive items, hold down the &#8220;Shift&#8221; key.<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox4-1.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox4-1.png?resize=874%2C858\" alt=\"\" width=\"874\" height=\"858\" class=\"alignleft size-full wp-image-341\" \/><\/a><\/p>\n<p>Then show the items you just selected by clicking on &#8220;Display Selected Items&#8221;:<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox5.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox5.png?resize=893%2C552\" alt=\"\" width=\"893\" height=\"552\" class=\"alignleft size-full wp-image-338\" \/><\/a><\/p>\n<p>Here is the code to loop through the selected items:<\/p>\n<pre class=\"lang:vb decode:true \" >Private Sub btnSelected_Click()\r\n    Dim intItem As Integer\r\n    Dim strItems As String\r\n    \r\n    For intItem = 0 To Me.lstSuppliers.ListCount - 1\r\n        \r\n        'if the item is selected then we are going to show it.\r\n        If Me.lstSuppliers.Selected(intItem) Then\r\n            \r\n            'display the selected items\r\n            MsgBox Me.lstSuppliers.Column(0, intItem)\r\n            \r\n        End If\r\n    Next intItem\r\nEnd Sub\r\n<\/pre>\n<p>Now you can use the selected ListBox items, and write them to other cells, or other places on your worksheet.<\/p>\n<p>Let me know if you have any questions.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>What is ListBox control? The list box allows you to see multiple items in one view and allows the user to select one or multiple items in the list. It helps you to manage a big worksheet, and allows you to put constraints on your data lists. You can design it with the ability to [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","om_disable_all_campaigns":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-327","post","type-post","status-publish","format-standard","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>How do you use ListBox in Excel? - My Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How do you use ListBox in Excel? - My Blog\" \/>\n<meta property=\"og:description\" content=\"What is ListBox control? The list box allows you to see multiple items in one view and allows the user to select one or multiple items in the list. It helps you to manage a big worksheet, and allows you to put constraints on your data lists. You can design it with the ability to [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"My Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-15T21:24:07+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-01-15T21:27:15+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox4-1.png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/01\\\/15\\\/how-do-you-use-listbox-in-excel\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/01\\\/15\\\/how-do-you-use-listbox-in-excel\\\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\"},\"headline\":\"How do you use ListBox in Excel?\",\"datePublished\":\"2019-01-15T21:24:07+00:00\",\"dateModified\":\"2019-01-15T21:27:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/01\\\/15\\\/how-do-you-use-listbox-in-excel\\\/\"},\"wordCount\":294,\"image\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/01\\\/15\\\/how-do-you-use-listbox-in-excel\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/01\\\/vba-listbox4-1.png\",\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/01\\\/15\\\/how-do-you-use-listbox-in-excel\\\/\",\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/01\\\/15\\\/how-do-you-use-listbox-in-excel\\\/\",\"name\":\"How do you use ListBox in Excel? - My Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/01\\\/15\\\/how-do-you-use-listbox-in-excel\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/01\\\/15\\\/how-do-you-use-listbox-in-excel\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/01\\\/vba-listbox4-1.png\",\"datePublished\":\"2019-01-15T21:24:07+00:00\",\"dateModified\":\"2019-01-15T21:27:15+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/01\\\/15\\\/how-do-you-use-listbox-in-excel\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/01\\\/15\\\/how-do-you-use-listbox-in-excel\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/01\\\/15\\\/how-do-you-use-listbox-in-excel\\\/#primaryimage\",\"url\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/01\\\/vba-listbox4-1.png\",\"contentUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/01\\\/vba-listbox4-1.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/01\\\/15\\\/how-do-you-use-listbox-in-excel\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How do you use ListBox in Excel?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/\",\"name\":\"My Blog\",\"description\":\"My WordPress Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"sameAs\":[\"https:\\\/\\\/vbastring.com\\\/blog\"],\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/author\\\/admin\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How do you use ListBox in Excel? - My Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/","og_locale":"en_US","og_type":"article","og_title":"How do you use ListBox in Excel? - My Blog","og_description":"What is ListBox control? The list box allows you to see multiple items in one view and allows the user to select one or multiple items in the list. It helps you to manage a big worksheet, and allows you to put constraints on your data lists. You can design it with the ability to [&hellip;]","og_url":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/","og_site_name":"My Blog","article_published_time":"2019-01-15T21:24:07+00:00","article_modified_time":"2019-01-15T21:27:15+00:00","og_image":[{"url":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox4-1.png","type":"","width":"","height":""}],"author":"admin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/#article","isPartOf":{"@id":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/"},"author":{"name":"admin","@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5"},"headline":"How do you use ListBox in Excel?","datePublished":"2019-01-15T21:24:07+00:00","dateModified":"2019-01-15T21:27:15+00:00","mainEntityOfPage":{"@id":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/"},"wordCount":294,"image":{"@id":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/#primaryimage"},"thumbnailUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox4-1.png","inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/","url":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/","name":"How do you use ListBox in Excel? - My Blog","isPartOf":{"@id":"https:\/\/vbastring.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/#primaryimage"},"image":{"@id":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/#primaryimage"},"thumbnailUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox4-1.png","datePublished":"2019-01-15T21:24:07+00:00","dateModified":"2019-01-15T21:27:15+00:00","author":{"@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5"},"breadcrumb":{"@id":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/#primaryimage","url":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox4-1.png","contentUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/01\/vba-listbox4-1.png"},{"@type":"BreadcrumbList","@id":"https:\/\/vbastring.com\/blog\/2019\/01\/15\/how-do-you-use-listbox-in-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vbastring.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How do you use ListBox in Excel?"}]},{"@type":"WebSite","@id":"https:\/\/vbastring.com\/blog\/#website","url":"https:\/\/vbastring.com\/blog\/","name":"My Blog","description":"My WordPress Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vbastring.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","caption":"admin"},"sameAs":["https:\/\/vbastring.com\/blog"],"url":"https:\/\/vbastring.com\/blog\/author\/admin\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/327","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/comments?post=327"}],"version-history":[{"count":5,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/327\/revisions"}],"predecessor-version":[{"id":347,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/327\/revisions\/347"}],"wp:attachment":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/media?parent=327"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/categories?post=327"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/tags?post=327"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}