This is a text and video example of how to implement a VBA Listbox:<\/p>\n
On your Excel worksheet show your “Forms Toolbar”. In Excel 2003 and prior, right click the standard toolbar (at the top of your screen) and choose “Forms Toolbar”. In Excel 2007 and above click the round Microsoft Office Button at the top left or your screen. Then choose “Excel Options” at the bottom of the list. The choose “Popular” and select the “Show Developer tab…” and select to show the developer tab.<\/p>\n
Add list box to your worksheet, then choose to show the properties. When you right click the listbox control, choose “Properties” and “Control”, then key in “A1” for the cell link. Call one of your worksheets “data”, and for “InputRange” enter “data!A1:A5” (that’s the naming structure [worksheet name]![cell range]). Make sure you enter some values into the sheet called “data” and cells “A1-A5” or else you won’t show anything in your new combobox.
\nThis is what your worksheet should look like:
\nCell A1 = “red”
\nCell A2 = “blue”
\nCell A3 = “yellow”
\nCell A4 = “green”
\nCell A5 = “brown”
\nIt’s going to look like this, and you’ll have to apply the principles to your situation:
\n************************************************
\nFor the single selection option<\/p>\n
Private Sub SelectSingleValue()\r\n\r\n Dim intCount As Integer\r\n Dim strTemp As String\r\n\r\n strTemp = lstColors.Value\r\n Range(\"A1\") = strTemp\r\n\r\nEnd Sub\r\n<\/pre>\n***********************************************
\n***********************************************
\nFor the multiple selection option<\/p>\nPrivate Sub SelectMultiValues()\r\n\r\n Dim intCount As Integer\r\n Dim strTemp As String\r\n Dim lngCellCount As Integer\r\n\r\n lngCellCount = 1\r\n\r\n For intCount = 0 To lstColors.ListCount - 1\r\n If lstColors.Selected(intCount) = True Then\r\n strTemp = lstColors.List(intCount)\r\n Range(\"A\" & lngCellCount) = strTemp\r\n\r\n lngCellCount = lngCellCount + 1\r\n End If\r\n Next\r\n\r\nEnd Sub<\/pre>\n*********************************************************
\nNow watch how it’s done (both the single and multiple select)…<\/p>\n