VBA ComboBox ListIndex
Excel & VBA ArticlesExcel UserForm Controls - ComboBox and ListBox: AddItem Method, BoundColumn, List Property, ListIndex, RowSource, Selected Property, ... Show UserForm Controls - ComboBox and ListBox ---------------------------------------------------------- Contents: Difference between ListBox and ComboBox Key Properties of ComboBox and ListBox Add Items/Data to (Populate) a ListBox or ComboBox Extract ListBox & ComboBox Items, with VBA Delete ListBox rows using the RemoveItem Method ---------------------------------------------------------- UserForm acts as a container in which you add multiple ActiveX controls, each of which has a specific use and associated properties. By itself, a UserForm will not be of much use unless ActiveX controls are added to it which are the actual user-interactive objects. Using ActiveX Controls on a Worksheet have been illustrated in detail, in the separate section of "Excel VBA: ActiveX Controls, Form Controls & AutoShapes on a Worksheet". An Excel VBA ListBox or ComboBox is a list of items from which a user can select. They facilitate in accepting data from users and making entries in an Excel worksheet. Difference between ListBox and ComboBox: 1. The ComboBox is a drop-down list (the user-entered item or the list-selected item is visible in the text area, whereas list values are visible by using the drop-down), while a ListBox shows a certain number of values with or without a scroll bar. In a ComboBox, only one row of items is visible at a given time (without using the drop-down) whereas in a ListBox one or more can be visible at a time. 2. In a ComboBox you can select ony one option from the list, while in a ListBox you can select multiple options from the list. 3. The user can enter his own item (in text area) in a ComboBox if it is not included in the list, which is not possible to do in a ListBox. In this sense, ComboBox is a combination of TextBox and ListBox. 4. CheckBox can be used within ListBox, but not within ComboBox. ListBox allows you to display a check box next to each item in the list, to enable user to select items (this might be easier for the user than using the multiple selection methods). To use CheckBoxes in a ListBox, set ListStyle property (in Properties Window) to fmListStyleOption (vba code: ListBox1.ListStyle = fmListStyleOption). This setting is best used with a multiselect ListBox. ------------------------------------------------------------------------------------------------------------------- Key Properties of ComboBox and ListBox Note1: All properties and methods given below are common to both ListBox and ComboBox, unless mentioned otherwise. Also refer "2. UserForm and Controls - Properties." for properties common to the UserForm and most Controls. Note 2: In below given examples, vba codes are required to be entered in the Code Module of the UserForm, unless specified otherwise. AddItem Method: Adds an item to the list, in a single-column ListBox or ComboBox. Adds a row to the list (ie. an item for each row), in a multi-column ListBox or ComboBox. Syntax: Control.AddItem(Item, Index). Item specifies the item or row to add. Index is an Integer which specifies the position where the new item or row is placed within the list, and if omitted, the item or row is added at the end. The item or row numbers begin with zero, and the first item or row is numbered 0, and so on. The value of Index cannot be greater than the total number of rows (ie. value of ListCount property). AddItem method will not work if ComboBox or ListBox is bound to data, hence RowSource data should be cleared before use. AddItem method can only be used with a macro or vba code. Note: AddItem method adds an item to the first column in a multi-column ListBox or ComboBox, and to add an item further to the first column, use the List or Column property specifying the item's row and column number. More than one row can also be added at a time to a ListBox or ComboBox by using the List or Column properties (AddItem adds one row at a time). This means that you can copy a two-dimensional array of values to a ListBox or ComboBox, using List or Column properties rather than adding each individual element using the AddItem method. Note: Using the Column property to copy a two-dimensional array of values to a ListBox or ComboBox, transposes the array contents and equates myArray(iRow, iColumn) to ListBox1.Column(iCol, iRow). List property copies an array without transposing it and myArray(iRow, iColumn) equates to ListBox1.List(iRow, iColumn). Refer Image 13 for example. BoundColumn Property: Specifies the column from which value is to be stored in a multicolumn ComboBox or ListBox, when a row is selected by the user. First column has a BoundColumn value of 1, second column has a value of 2, and so on. Setting the BoundColumn value to 1 will assign the value from column 1 to the ComboBox or ListBox, and so on. BoundColumn property lets the user to store a different set of values per specified column while TextColumn property displays one set of values, viz.use the Text property to return the value from the first column (specified in the TextColumn property) containing the names and the BoundColumn property can specify another column containing height wherein on selecting a particular person's name in the ListBox, his height will get returned or stored (refer Image 10). The ColumnWidths property of a column can be set to zero to not display it in the ListBox. Setting the BoundColumn value to 0 assigns the value of the ListIndex property (which is the number of the selected row) as the value of the control (ComboBox or ListBox). This setting is useful if you want to determine the row of the selected item in a ComboBox or ListBox. BoundColumn Property can be set in the Properties window and can also be used with a macro or vba code. Note: Where the ControlSource mentions =Sheet3!D2 (vba code: .ControlSource = "=Sheet3!D2"), the value in the BoundColumn of the selected row will get stored in cell D2, Sheet3. Example 1: Setting the BoundColumn value to 0 assigns the value of the ListIndex property (which is the number of the selected row) as the value of the control (in a Single Selection ListBox) - refer Image 7 Private Sub UserForm_Initialize()
.ColumnHeads = True .ColumnCount = 2 'ColumnWidths property of the second column is set to zero to not display it in the ListBox. .ColumnWidths = "50;0" .RowSource = "=Sheet3!A2:B6" .MultiSelect = fmMultiSelectSingle .BoundColumn = 0 End With
Private Sub CommandButton1_Click()
TextBox1.Value = ListBox1.Value + 2 End If
Clear Method: Removes all items in a ComboBox or ListBox. Syntax: Control.Clear. Clear method will not work if ComboBox or ListBox is bound to data, hence RowSource data should be cleared before use. Clear method can only be used with a macro or vba code. Column Property: Refers to a specific column, or column and row combination, in a multiple-column ComboBox or ListBox. Syntax: Control.Column(iColumn, iRow). Column property can only be used with a macro or vba code and is not available at design time. iColumn specifies the column number wherein iColumn = 0 means the first column in the List. iRow specifies the row number wherein iRow = 0 means the first row in the List. Both iColumn and iRow are integer values ranging from 0 to number of columns and rows (respectively) in the list minus 1. Specifying both column and row numbers will refer to a specific item, and specifying only the column number will refer to a specific column in the current row viz. ListBox1.Column(1) refers the second column. You can copy a two-dimensional array of values to a ListBox or ComboBox, using Column (or List) property rather than adding each individual element using the AddItem method. Column property can be used to assign the contents of a ComboBox or ListBox to another control, viz. TextBox (refer Image 8). Note: Using the Column property to copy a two-dimensional array of values to a ListBox or ComboBox, transposes the array contents and equates myArray(iRow, iColumn) to ListBox1.Column(iCol, iRow). List property copies an array without transposing it and myArray(iRow, iColumn) equates to ListBox1.List(iRow, iColumn). Refer Image 13 for example. Example 2: Load ListBox using AddItem method and List & Column properties; and use Column property to assign the contents of ListBox to TextBox - refer Image 8 Private Sub UserForm_Initialize()
.ColumnCount = 3 .ColumnWidths = "50;50;50" .ColumnHeads = False .RowSource = "=Sheet2!A2:B6" .MultiSelect = fmMultiSelectMulti End With 'clearing the TextBox if it is not empty
Private Sub CommandButton1_Click() 'AddItem method will not work if ListBox is bound to data, hence RowSource is cleared if it had been set 'Create a new row with AddItem ListBox1.AddItem "banana"
'add item in second column of this second row, using Column property 'Create a new row with AddItem and position as row number 1 'adding items in the 3 columns and positioning this row as the first row - this will push down the above two rows 'item in column number 3 and row number 2 of ListBox
ColumnCount Property: Specifies the number of columns to be displayed in a ComboBox or ListBox. A ColumnCount value of 0 does not display any column and a setting of -1 displays all columns. ColumnCount property can be set in the Properties window and can also be used with a macro or vba code. ColumnHeads Property: A Boolean value (True/False) which determines display of column headings (in a single row) for ComboBox or ListBox. ColumnHeads property can be set in the Properties window and can also be used with a macro or vba code. Column Headings can be displayed only if ColumnHeads is set to True in Properties window (VBA code: ListBox1.ColumnHeads = True) and if you bind the ListBox to a range (ie. set RowSource to a range that includes headings). Note: AddItem method will not work if ListBox or ComboBox is bound to data, hence RowSource property should be cleared for using AddItem. List Property: List Property is used in conjunction with the ListCount and ListIndex properties to return items in a ListBox or ComboBox control. Syntax -> Control.List(iRow,iCol). Each item in a list has a row number and a column number, wherein row and column numbers start with zero. iRow specifies the row number wherein iRow = 2 means the third row in the List. iColumn specifies the column number wherein iColumn = 0 means the first column in the List. Omitting to specify the iColumn will retrieve the first column. Specify iColumn only for a multi-column ListBox or ComboBox. List Property can only be used with a macro or vba code and is not available at design time. Note: To copy a two-dimensional array of values to a ListBox or ComboBox, use List or Column properties. To add a one-dimensional array or to add an individual element, use the AddItem method. Items can be removed from a List using the RemoveItem method. List property is available only by using a macro or VBA. Example 3: Use Selected & List properties to display multiple-selected ListBox items (choose any column to display) in TextBox, and link a worksheet cell with TextBox using ControlSource property - refer Image 9. Private Sub UserForm_Initialize()
.ColumnHeads = True .ColumnCount = 2 'ColumnWidths property of the second column is set to zero to not display it in the ListBox. .ColumnWidths = "50;0" .RowSource = "=Sheet3!A2:B6" .MultiSelect = fmMultiSelectMulti .TextColumn = 1 End With
.MultiLine = True 'the text or value in the TextBox will get stored in the worksheet cell - Sheet3!F2 End With
Private Sub CommandButton1_Click()
'check all items in a ListBox 'if a ListBox item is selected, it will display in TextBox If TextBox1.Value = "" Then 'ListBox1.List(n, 0) or ListBox1.List(n)displays the first column in TextBox, ListBox1.List(n, 1) displays the second column and so on Else 'alternate code which displays the second column in TextBox: TextBox1.Value = TextBox1.Value & vbCrLf & Range(ListBox1.RowSource).Offset(n, 1).Resize(1, 1).Value TextBox1.Value = TextBox1.Value & vbCrLf & ListBox1.List(n, 1) End If End If Next n
ListCount Property: Determines the total number of rows in a ListBox or ComboBox. This property can only be used with a macro or vba code and is not available at design time. Note: The column headings row is also counted, if ColumnHeads are displayed. The ListCount property can be used with the ListRows property to specify the number of rows to display in a ComboBox. ListIndex Property: Determines which item is selected in a ComboBox or ListBox. The first item in a list has a ListIndex value of 0, the second item has a value of 1, and so on. Hence, it is an integer value ranging from 0 to the total number of items in a ComboBox or ListBox minus 1. ListIndex returns -1 when no rows are selected. This property can only be used with a macro or vba code and is not available at design time. Note: In a Multiple Selection enabled ListBox, ListIndex returns the index of the row that has focus, irrespective of whether that row is selected or not. Hence the Selected property of the ListBox (and not the ListIndex property) shoud be used here to return and set a selection. In a Single Selection enabled ListBox (viz. MultiSelect property setting of fmMultiSelectSingle), ListIndex returns the index of the selected item and hence ListIndex property should be used here to return and set a selection. ListRows Property: Specifies the maximum number of rows which will display in the list box portion of a ComboBox. The default value is 8. Note: If the actual number of list items exceed this maximum value of the ListRows property, a vertical scroll bar will appear in the list box portion of the ComboBox (and the excess list items can be viewed by scrolling down). The ListCount property can be used with the ListRows property to specify the number of rows to display in a ComboBox. ListRows property can be set in the Properties window and can also be used with a macro or vba code. ListRows Property is valid for ComboBox and not for ListBox. Example 4: Using the ListCount property with the ListRows property, to set number of rows to display in ComboBox Private Sub UserForm_Initialize()
If .ListCount > 5 Then .ListRows = 5 Else .ListRows = .ListCount End If End With
MultiSelect Property: Specifies whether multiple selections are allowed. There are 3 settings: (i) fmMultiSelectSingle (value 0), the default setting, wherein only a single item can be selected; (ii) fmMultiSelectMulti (value 1) which allows multiple selections wherein an item can be selected or deselected by clicking mouse or pressing SPACEBAR; and (iii) fmMultiSelectExtended (value 2) which allows multiple selections, wherein by pressing SHIFT and simultaneously moving the up or down arrows (or pressing SHIFT and clicking mouse) continues selection from the previously selected item to the current selection (ie. a continuous selection); this option also allows to select or deselect an item by pressing CTRL and clicking mouse. MultiSelect property can be set in the Properties window and can also be used with a macro or vba code. Note: MultiSelect Property is valid for ListBox and not for ComboBox. When multiple selections are made (viz. fmMultiSelectMulti or fmMultiSelectExtended), the selected items can be determined only by using the Selected property (Selected property is available by using macro) of the ListBox. The Selected property will have values ranging from 0 to ListCount minus 1 and will be True if the item is selected and False if not selected. The Selected property determines the items you chose, and the List property returns the items. Example 5: Determining selected item in a Single Selection ListBox, in VBA: Private Sub CommandButton1_Click() 'alternatively: If ListBox1.ListIndex >= 0 Then MsgBox ListBox1.Value End If
RemoveItem Method: A specified row is removed from the list in a ComboBox or ListBox. Syntax: Control.RemoveItem(Row_Index). Row_Index is the row number which is specified to be removed, wherein the first row is numbered 0, and so on. RemoveItem method will not work if ComboBox or ListBox is bound to data, hence RowSource data should be cleared before use. RemoveItem method can only be used with a macro or vba code. RowSource Property: Specifies the source of a list (which could be a worksheet range in Excel), for a ComboBox or ListBox. RowSource property can be set in the Properties window and can also be used with a macro or vba code. To set RowSource property in Properties window, enter without inverted commas: "=Sheet2!A2:A6" which populates ComboBox or ListBox with values in cells A2:A6 in Sheet2. VBA code for this is: ListBox1.RowSource = "=Sheet2!A2:A6". It is not necessary to use the equal mark in "=Sheet2!A2:A6" while setting the property and ListBox1.RowSource = "Sheet2!A2:A6" will have the same effect. Selected Property: Specifies whether an item is selected in a ListBox control. Syntax: Control.Selected(Item_Index). Returns True/False if the item is Selected/NotSelected; Set to True/False to select the item or remove selection [viz. Control.Selected(ItemIndex) = True/False]. Item_Index is an integer value ranging from 0 to number of items in the list minus 1, indicating its relative position in the list, viz. ListBox.Selected(2) = True selects the third item in the list. Selected property is particularly useful when working with multiple selections. Selected Property can only be used with a macro or vba code and is not available at design time. Note1: In a Multiple Selection enabled ListBox, ListIndex returns the index of the row that has focus, irrespective of whether that row is selected or not. Hence the Selected property of the ListBox (and not the ListIndex property) shoud be used here to return and set a selection. In a Single Selection enabled ListBox (viz. MultiSelect property setting of fmMultiSelectSingle), ListIndex returns the index of the selected item and hence ListIndex property should be used here to return and set a selection. Note2: Selected Property is valid for ListBox and not for ComboBox. Example 6: Determining selected items in a multiple-selection enabled ListBox using Selected & List properties: Private Sub CommandButton1_Click() 'check all items in a ListBox 'if a ListBox item is selected, it will display in MsgBox If ListBox1.Selected(n) = True Then 'display a selected item End If Next n
Style Property: Valid for ComboBox only, not for ListBox. This property determines choosing or setting the value of ComboBox. There are 2 settings: (i) fmStyleDropDownCombo (value 0). The user has both options of typing a custom value in the text area or select from the drop-down list. This is the default value.; (ii) fmStyleDropDownList (value 2). The user can only select from the drop-down list, like in ListBox. Style Property can be set in the Properties window and can also be used with a macro or vba code. TextColumn Property: Specifies the column of data in a ListBox that supplies data for its Text property - the TextColumn property determines the column whose value the Text property will return whereas the BoundColumn property determines the column whose value the Value property returns. The Text property returns the same as Value property if the TextColumn property is not set. First column has a TextColumn value of 1, second column has a value of 2, and so on. Setting the TextColumn value to -1 indicates that the first column with a ColumnWidths value greater than 0 will be displayed. TextColumn property enables display of one set of values to the user but store a different set of values (per column specified in the BoundColumn property) viz. use the Text property to return the value from the first column (specified in the TextColumn property) containing the names and the BoundColumn property can specify another column containing height wherein on selecting a particular person's name in the ListBox, his name & height will be returned. The ColumnWidths property of any column can be set to zero to not display it in the ListBox. Setting the TextColumn value to 0 displays the ListIndex value (which is the number of the selected row) in TextColumn Property - this setting is useful if you want to determine the row of the selected item. TextColumn property can be set in the Properties window and can also be used with a macro or vba code. Note: In a ComboBox, when a user selects an item, the column specified in the TextColumn property will be displayed in the ComboBox's text box portion. Example 7:Display first column in the List and use the TextColumn & BoundColumn Properties to return values from first & third columns (in a Single Selection ListBox) - refer Image 10 Private Sub UserForm_Initialize()
.ColumnHeads = True .ColumnCount = 3 'set the ColumnWidths property of second & third columns to zero to not display them in the ListBox .ColumnWidths = "40;0:0" .RowSource = "=Sheet2!A2:C6" .MultiSelect = fmMultiSelectSingle 'specifies the column of data in a ListBox that supplies data for its Text property .TextColumn = 1 .BoundColumn = 3 End With
Private Sub CommandButton1_Click() 'works only if MultiSelect Property of ListBox is set to fmMultiSelectSingle which allows single selection. 'use the ListBox Text property to return the value from the column specified in the TextColumn column, whereas the ListBox Value property returns the value from the column specified in the BoundColumn property TextBox1.Value = ListBox1.Text & " - " & ListBox1.Value & " cms" End If
-------------------------------------------------------------------------------------------------------------------- Add Items/Data to (Populate) a ListBox or ComboBox 1. Setting the RowSource property of a ListBox or ComboBox in a UserForm VBA code - if the list is static: Me.ListBox1.RowSource = "Sheet1!A1:B6" or VBA code - if the list is dynamic: Me.ListBox1.RowSource = "Sheet1!A1:B" & Sheet1.Cells(Rows.Count, "B").End(xlUp).Row Note: You can set the RowSource property of a ListBox or ComboBox in the Properties Window (without using vba code), by entering -> Sheet1!A1:B6 Example 8: Populate ComboBox by setting the RowSource property to a named list - refer Image 11 Private Sub UserForm_Initialize()
.ColumnCount = 2 .ColumnWidths = "50;50" .ColumnHeads = True 'For a named list (viz. HeightList in Range A2:B6), the RowSource property can be set to Sheet1!HeightList .RowSource = "Sheet1!HeightList" End With
2. Populate a ComboBox or ListBox from an Array: VBA code - populate single column in ListBox: ListBox1.List = Array("RowOne", "RowTwo", "RowThree", "RowFour") VBA code - populate single column in ComboBox: ComboBox1.List = Array("Apples", "Bananas", "Oranges", "Pears") VBA code - populate ListBox from array named myArray: Dim myArray As Variant VBA code - Populate single column ComboBox: Dim i As Integer Me.ComboBox1.AddItem myArray(i) Next Example 9 - Populate a multi-column Listbox directly with Worksheet Range - multiple rows added at one time using the List property: Private Sub UserForm_Initialize()
.ColumnCount = 3 .ColumnWidths = "50;50;50" .ColumnHeads = False End With
Dim rng As Range Set rng = Sheet1.Range("A1:C6") Me.ListBox1.List = rng.Cells.Value
Example 10 - Populate a multi-column Listbox directly with Worksheet Range - multiple rows added at one time using the List property: Private Sub UserForm_Initialize()
.ColumnCount = 3 .ColumnWidths = "50;50;50" .ColumnHeads = False End With
Dim var As Variant var = Sheet1.Range("A1:C6") Me.ListBox1.List = var
Example 11: Load Worksheet Range to a multi-column ListBox, after placing Range data in a 2-dimensional Array - refer Image 12 Option Base 1
'if Option Base 1 was not set, this line of code should be: ReDim myArray(1 To totalRows, 1 To totalColumns)
For iRow = 1 To totalRows For iCol = 1 To totalColumns myArray(iRow, iCol) = rng.Cells(iRow, iCol) Next iCol Next iRow Next
With ListBox1 .ColumnCount = 3 .ColumnWidths = "50;50;50" .ColumnHeads = False .List = myArray End With
Example 12: Load a 2-dimensional array to ListBox using the List property (copies an array without transposing it) and Column property (which transposes the contents of the array) - refer Image 13 Private Sub UserForm_Initialize()
.ColumnCount = 3 .ColumnWidths = "50;50;50" .ColumnHeads = False End With With ListBox2 .ColumnCount = 3 .ColumnWidths = "50;50;50" .ColumnHeads = False End With
Private Sub CommandButton1_Click() 'Declaring the array and its dimensions. The array has been named myArray, of size 3 by 3 (three rows by three columns). Note: When you populate an array with data, the array will start at zero, and if you include Option Base 1 the array will start at 1.
For n = 0 To 2 myArray(n, 0) = n + 1 Next n
'copies an array without transposing it
3. Populate a ComboBox or ListBox with AddItem method Example 13: Populate a single-column ListBox from worksheet range Private Sub UserForm_Initialize()
.ColumnCount = 1 .ColumnWidths = "50" .ColumnHeads = False 'AddItem method will not work if ListBox is bound to data, hence RowSource is cleared if it had been set .RowSource = "" End With
For Each cell In rng.Cells Me.ListBox1.AddItem cell.Value Next cell
Example 14: Populate a single-column ListBox with values from 1 to 500 Private Sub UserForm_Initialize()
.ColumnCount = 1 .ColumnWidths = "50" 'AddItem method will not work if ListBox is bound to data, hence RowSource is cleared if it had been set .RowSource = "" End With
.AddItem "N/A" For i = 1 To 500 .AddItem i Next i End With
Example 15: Create a new row with AddItem and specify its row number - refer Image 14 Private Sub UserForm_Initialize()
.ColumnCount = 1 .ColumnWidths = "50" .ColumnHeads = False 'AddItem method will not work if ListBox is bound to data, hence RowSource is cleared if it had been set .RowSource = "" End With
'Create a new row with AddItem and position as row number 1 - this will push down the above two rows
Example 16: Populate a ComboBox with the 12 months in a year - Refer Image 15 Private Sub UserForm_Initialize()
.ColumnCount = 1 .ColumnWidths = "50" .ColumnHeads = False 'AddItem method will not work if ComboBox is bound to data, hence RowSource is cleared if it had been set .RowSource = "" End With
For n = 1 To 12 ComboBox1.AddItem Format(DateSerial(2011, n, 1), "mmmm") Next n
4. Populate a multi-column ComboBox or ListBox using AddItem method and List & Column properties Example 17: refer Image 16 Private Sub UserForm_Initialize()
.ColumnCount = 3 .ColumnWidths = "50;50;50" .ColumnHeads = False 'AddItem method will not work if ComboBox is bound to data, hence RowSource is cleared if it had been set .RowSource = "" End With
'Create a new row with Additem ComboBox1.AddItem "orange" 'add item in second column of this second row, using Column property 'Create a new row with Additem and position as row number 1 'adding items in the 3 columns and positioning this row as the first row - this will push down the above two rows End Sub 5. Populate a multi-column ListBox from a worskheet range, using AddItem method and List property Example 18: refer Image 17 Private Sub UserForm_Initialize()
.ColumnCount = 3 .ColumnWidths = "50;50;50" 'AddItem method will not work if ListBox is bound to data, hence RowSource is cleared if it had been set .RowSource = "" End With
Private Sub CommandButton1_Click()
'determine total number of rows in column A
With Me.ListBox1 counter = counter + 1 'create a new row with Additem .List(.ListCount - 1, 2) = Sheet4.Cells(counter, 1).Offset(0, 2).Value End With Loop Until counter = totalRows
6. Add a new item/row to the list if ComboBox is bound to data in a worksheet. Example 19: refer Images 18a & 18b Private Sub UserForm_Initialize()
.ColumnCount = 3 .ColumnWidths = "50;50;50" .ColumnHeads = True .BoundColumn = 1 'a named-range (name: "cbRange") has been created in Sheet3 of the workbook, using the Name Manager: "=Sheet3!$A$2:$C$6" .RowSource = "cbRange" End With
Private Sub CommandButton1_Click()
'determine first column of the named-range "cbRange" 'create a new single-column named-range (name: "cbRangeTemp"), populated with only the first column of the named-range "cbRange". 'checks if ComboBox1.Value is already existing in column 1 of named-range "cbRange" 'resizing the named-range "cbRange", to add another worksheet row at the end, wherein the ComboBox1.Value will get posted: Range("cbRange").Resize(Range("cbRange").Rows.Count + 1).Name = "cbRange" ComboBox1.RowSource = "cbRange" 'posting columns of the new row with values from ComboBox1, TextBox1 & TextBox2: Sheet3.Cells(Range("cbRange").Rows.Count + 1, colNo) = ComboBox1.Value Sheet3.Cells(Range("cbRange").Rows.Count + 1, colNo).Offset(0, 1) = TextBox1.Text Sheet3.Cells(Range("cbRange").Rows.Count + 1, colNo).Offset(0, 2) = TextBox2.Text Else MsgBox "Item already in List" End If ComboBox1.Value = ""
------------------------------------------------------------------------------------------------------------------------------------------- Extract ListBox & ComboBox Items, with VBA VBA code - Display selected ComboBox item in TextBox: 'the text area of ComboBox shows the item entered by user of his own choice or that selected from list items, and this item gets displayed in TextBox TextBox1.Value = ComboBox1.Value Note: VBA code-> TextBox1.Value = ListBox1.Value, or TextBox1.Text = ListBox1.Value, will work only in case MultiSelect property of ListBox is set to fmMultiSelectSingle, ie. in case of a single-selection enabled ListBox. It will copy the selected item (value in BoundColumn) from the list. VBA code - Copy selected ComboBox item to a worksheet range: 'the text area of ComboBox shows the item entered by user of his own choice or that selected from list items, and this item is copied to the worksheet range Sheet1.Range("G4").Value = ComboBox1.Value Note: VBA code-> Sheet4.Range("G4").Value = ListBox1.Value, will work only in case MultiSelect property of ListBox is set to fmMultiSelectSingle, ie. in case of a single-selection enabled ListBox. It will copy the selected item (value in BoundColumn) from the list. VBA code - Copy ComboBox item determined by its position, to a worksheet range: 'an existing ComboBox item, determined by its position (row 4, column 1), posted to a worksheet cell Sheet1.Range("F2").Value = ComboBox1.List(3, 0) Note: VBA code for ListBox -> Sheet1.Range("F2").Value = ListBox1.List(3, 0) Example 20: Extracting ListBox items (of multi-column ListBox) to a worksheet range - refer Image 19 Private Sub UserForm_Initialize()
.ColumnCount = 3 .ColumnWidths = "50;50;50" .ColumnHeads = True .RowSource = "Sheet2!A2:C6" .MultiSelect = fmMultiSelectMulti End With
Private Sub CommandButton1_Click() 'check all items/rows in a ListBox 'if a ListBox row is selected, it will get copied to the worksheet range If ListBox1.Selected(r) = True Then 'copying multi-column ListBox rows to corresponding/matching worksheet rows & columns: For c = 1 To ListBox1.ColumnCount Sheet1.Cells(r + 1, c).Value = ListBox1.List(r, c - 1) Next c End If Next r
Example 21: Extract multiple items in a row from a single-selection enabled & multi-column ListBox, and copy to worksheet range - refer Image 20 Private Sub UserForm_Initialize()
.ColumnCount = 3 .ColumnWidths = "50;50;50" .ColumnHeads = True .BoundColumn = 1 .MultiSelect = fmMultiSelectSingle .RowSource = "Sheet3!A2:C6" End With
Private Sub CommandButton1_Click()
rng.Value = ListBox1.Value rng.Offset(0, 1).Value = ListBox1.List(ListBox1.ListIndex, 1) rng.Offset(0, 2).Value = ListBox1.List(ListBox1.ListIndex, 2) End If Example 22: Select or enter name in ComboBox, and lookup its corresponding Grade in a worksheet range - refer Image 21 Private Sub UserForm_Initialize()
.ColumnCount = 1 .ColumnWidths = "50" .ColumnHeads = True .RowSource = "Sheet3!A2:B6" End With
With TextBox1 .Enabled = False End With
Private Sub CommandButton1_Click()
'determine total number of rows in column B
'Name selected in ComboBox is posted to TextBox
'check if name selected or entered in ComboBox is present in the lookup range: If Application.CountIf(Sheet3.Range("A1:A" & totalRows), TextBox1.Text) > 0 Then 'lookup Grade of selected Name, in the worksheet range Sheet3.Cells(1, 4).Value = TextBox1.Text & "'s grade is " & Application.VLookup(TextBox1.Text, Sheet3.Range("A1:B" & totalRows), 2, False) Else MsgBox "Name not found!" End If End If
------------------------------------------------------------------------------------------------------------------------------------------- Delete ListBox rows using the RemoveItem Method Example 23: Use RemoveItem method to delete a ListBox row. The below code deletes the row from the ListBox and also deletes the row items (or rows) in the worksheet - refer Images 22a and 22b. Private Sub UserForm_Initialize()
.ColumnCount = 3 .ColumnWidths = "50;50;50" .ColumnHeads = False .MultiSelect = fmMultiSelectMulti End With
'determine total number of rows in column A
'removes all items in ListBox
Private Sub CommandButton1_Click()
'check all items in a ListBox; reverse order (Step -1) is used because rows are being deleted from ListBox. If ListBox1.Selected(n) = True Then 'item to be deleted is stored in the variable named var var = ListBox1.List(n, 0)
Sheet3.Cells(i, 1).Offset(0, 1) = "" 'use this code instead of the preceding 3-lines, to delete the determined row in the worksheet End If Next n
Example 24: Delete all rows in ListBox, using RemoveItem method Private Sub UserForm_Initialize()
.ColumnCount = 3 .ColumnWidths = "50;50;50" .BoundColumn = 1 .MultiSelect = fmMultiSelectMulti 'RemoveItem method will not work if ListBox is bound to data, hence RowSource is cleared if it had been set ListBox1.RowSource = "" End With
With Me.ListBox1 'create a new row with Additem .AddItem Sheet3.Cells(n, 1).Value .List(.ListCount - 1, 1) = Sheet3.Cells(n, 1).Offset(0, 1).Value .List(.ListCount - 1, 2) = Sheet3.Cells(n, 1).Offset(0, 2).Value End With Next n
Private Sub CommandButton1_Click()
'Note: "ListBox1.RemoveItem 0" is the same as "ListBox1.RemoveItem (0)" 'alternate code: ListBox1.RemoveItem 0 ListBox1.RemoveItem ListBox1.ListCount - 1 Next n
|