Double-clicking On The Listbox Control Registers Which Default Event?
The VBA ListBox is a very useful command. If yous are creating any kind of UserForm application you volition most likely use it.
In this post, I'1000 going to show you lot everything you need to know near the VBA ListBox so y'all can avoid the common pitfalls and get up and running quickly and easily.
Contents
- 1 What is the VBA ListBox used for?
- 1.i VBA ListBox versus the VBA ComboBox
- two The VBA ListBox Properties Quick Guide
- 3 How to Add together Items to the ListBox
- 3.one VBA ListBox Listing Property
- 3.1.1 The List Property and Column Headers
- 3.ane.ii Updating Items using the List Property
- iii.2 VBA ListBox RowSource
- 3.2.ane How to use RowSource
- iii.ii.2 RowSource Cavalcade Headers
- iii.three VBA ListBox AddItem
- 3.one VBA ListBox Listing Property
- 4 VBA ListBox Selected Items
- 5 Reading Data from the VBA Listbox
- 5.i Single selection only with 1 column
- five.ii Single selection only with multiple columns
- 5.3 Multiple selections
- 6 VBA ListBox MultiSelect
- 7 VBA ListBox Columns
- 8 VBA ListBox Column Headers
- ix Creating a ListBox Dynamically
- ten Loop through ListBoxes
- 11 YouTube Video
- 12 What's Side by side?
What is the VBA ListBox used for?
The ListBox is used to display a list of items to the user so that the user can then select one or more than. The ListBox can take multiple columns and then it is useful for tasks similar displaying records.
VBA ListBox versus the VBA ComboBox
The ListBox is very similar to the ComboBox which besides allows the user to select an particular from a list of items. The chief differences are:
- The Listbox allows multiple selections. The Combobox simply allows one selection.
- Items in the ListBox are e'er visible. The Combobox items are only visible when you click on the "down" icon.
- The ComboBox has the ability to filter the contents when you type.
The VBA ListBox Properties Quick Guide
| Part | Operation | Example |
|---|---|---|
| AddItem | Add an particular | listbox.AddItem "Spain" |
| Clear | Remove all Items | listbox.Clear |
| ColumnCount | Gear up the number of visible columns | ComboBox1.ColumnCount = 2 |
| ColumnHeads | Make the cavalcade row visible | ComboBox1.ColumnHeads = True |
| List | Range to Listbox ListBox to Range | Listbox.List = Range("A1:A4").Value Range("A1:A4").Value = Listbox.List |
| List | Update a column value | Listbox.List(ane,ii) = "New value" |
| ListCount | Become the number of items | cnt = listbox.ListCount |
| ListIndex | Get/set selected item | Idx = listbox.ListIndex combo.ListIndex = 0 |
| RemoveItem | Remove an item | listbox.Remove 1 |
| RowSource | Add a range of values from a worksheet | ComboBox1.RowSource = Sheet1.Range("A2:B3").Accost |
| Value | Get the value of selected Item | Dim sCountry As Cord sCountry = listbox.Value |
How to Add Items to the ListBox
There are 3 ways to add items to the VBA Listbox:
- One at a time using the AddItem belongings.
- Calculation an array/range using the List belongings.
- Adding a Range using the RowSourceholding.
The List and RowSource backdrop are the most commonly used. The table below provides a quick comparison of these properties:
| Task | RowSource | List |
|---|---|---|
| Column Headers | Yes | No |
| Update values in ListBox | No | Aye |
| Add new items | No | Yes |
| Data type | Range | Array(including Range.Value) |
| If source data changes | Listbox is automatically updated. | ListBox is not updated. |
VBA ListBox Listing Property
The List holding allows you to add together to contents of an assortment to a ListBox. Every bit Range.Value is an array you can re-create the contents of any range to the Listbox.
Here are some examples of using the List holding:
' Add the contents of an assortment ListBox1.List = Array("Apple tree", "Orange", "Banana") ' Add the contents of a Range ListBox1.List = Range("A1:E5").Value
You can likewise use the Listing property to write from the ListBox to an array or range:
Range("A1:B3").Value = ListBox1.List Important Note: If there is only 1 particular in a range and so VBA doesn't covert it to an array. Instead, information technology converts the range to a cord/double/date etc.
Sheet1.Range("A1:A2").Value ' Array Sheet1.Range("A1").Value ' Unmarried value variable
In this case, you need to use AddItem to add together the value to the ListBox:
If myRange.Count = 1 Then ListBox1.AddItem myRange Else ListBox1.List = myRange.Value End If
The List Property and Cavalcade Headers
The ListBox simply displays cavalcade headers if you lot use RowSource. Otherwise, they are not available. The all-time fashion to add together column headers(and information technology's not a great way) is to add Labels in a higher place the ListBox columns. One advantage is that y'all can utilise the click issue of the Label if you want to implement something like sorting.
Updating Items using the List Property
You can update individual items in the ListBox using the Listing Property.
Imagine we take a ListBox with information similar this:
If we want to change Nelson in row three, column two nosotros do it like this:
ListBox1.List(two, 1) = "SMITH" The result nosotros get is:
The List holding rows and columns are zero-based so this means row 1 is 0, row 2 is 1, row iii is 2 and and then on:
VBA ListBox RowSource
The RowSource holding allows us to add a range to the ListBox. This is different from the List Property in that the Range is linked to the ListBox. If information in the Range changes so the data in the ListBox will update automatically.
When we use RowSource the data in the ListBox is read-but. We tin can change the RowSource range merely we cannot change the values in the ListBox.
How to utilize RowSource
We add the RowSource range as a string similar this:
ListBox1.RowSource = "Sheet1!A1:A5" If yous don't specify the sheet the VBA will utilize the active sheet
ListBox1.RowSource = "A1:A5" If you are using the Address of a range object with RowSource then information technology is of import to use the External parameter. This volition ensure that RowSource will read from the sheet of the range rather than the active canvass:
' Get the range Dim rg As Range Set rg = Sheet1.Range("A1:A5") ' Accost will be $A$ane:$A$5 which volition use the active sheet ListBox1.RowSource = rg.Accost Debug.Impress ListBox1.RowSource ' Address will be [Book2]Sheet1!$A$1:$A$5 which volition apply Sheet1 ListBox1.RowSource = rg.Address(External:=True) Debug.Impress ListBox1.RowSource
RowSource Cavalcade Headers
Column headers are automatically added to the ListBox when you use the RowSource belongings. The ColumnHeads property must be set to True or the headers volition not announced. Y'all tin set this property in the code or in the properties window of the ListBox.
ListBox1.ColumnHeads = Truthful The column headers are taken from the row above the range used for the RowSource. For example, if your range is A2 to C5 then the column header will utilise the range A1 to C1:
Here is an instance: We want to add together the information below to our ListBox and we want A1 to C1 to be the header.
Nosotros set the RowSource property to A2:C5 and set the ColumnHeads property to truthful:
With ListBox1 .RowSource = "sheet1!A2:C5" .ColumnHeads = Truthful .ColumnWidths = "80;eighty;80" Stop With
The result will look like this:
VBA ListBox AddItem
It is very rare that you would utilize the AddItem property to fill up the ListBox. List and RowSource are much more efficient. AddItem is normally used when the Listbox already has items and you want to add a new item.
The AddItem property is simple to apply. You provide the item you want to add every bit a parameter. The ListBox will automatically add together information technology as the last item:
With ListBox .AddItem "Apple" .AddItem "Orange" End With
If you want to Insert the item at a sure position you lot can employ the 2d parameter. Go along in heed that this is a zero-based position, so if you want the item in position one then the value is 0, position 2 the value is one, so on.
With ListBox1 .AddItem "Apple" .AddItem "Orange" ' Add "Banana" to position 1(Index 0) .AddItem "Banana", 0 End With
The guild volition be:
Banana
Apple tree
Orange
If you desire to add together multiple columns with AddItem then y'all need to use the List holding after yous use AddItem:
With listboxFruit .Listing = myRange.Value .AddItem "Assistant" ' Add together to the 2d column of 'Assistant' row .Listing(2, ane) = "$ii.99" Terminate With
Ane reason for using AddItem is if you are calculation from data that isn't sequential so you cannot use the List or RowSource properties:
Dim cell As Range ' Fill items with first alphabetic character is A For Each cell In Sheet1.Range("A1:A50") If Left(cell.Value, 1) = "A" Then comboBoxFruit.AddItem jail cell.Value Terminate If Next
Of import Note: If y'all fill up a ListBox with RowSource then you lot cannot utilise AddItem to add a new particular. If you try you will become a "Runtime Error seventy – Permission Denied".
VBA ListBox Selected Items
If but ane item is selected and then you tin utilise ListIndex to get the selected row. Remember that information technology is nix-based so row ane in the ListBox is at ListIndex 0, row two at ListIndex 1 and so on.
MsgBox "The selected item is " & ListBox1.ListIndex If the ListBox has multiple columns then you lot can apply the ListIndex and List properties together to return a value in the selected row:
' Display the value from the second cavalcade of the selected row MsgBox ListBox1.List(ListBox1.ListIndex, 2) If multiple items are selected and so you tin can employ the GetSelectedRows function which returns a drove of selected rows:
Sub Example() ' Store the row numbers of selected items to a collection Dim selectedRows As Collection Set selectedRows = GetSelectedRows() ' Print the selected rows numbers to the Immediate Window Dim row As Long For Each row In selectedRows ' Impress to the Firsthand Window Ctrl + M Debug.Impress row Adjacent row End Sub ' Returns a drove of all the selected items Function GetSelectedRows() As Collection ' Create the drove Dim coll As New Collection ' Read through each item in the listbox Dim i As Long For i = 0 To listboxFruit.ListCount - ane ' Check if detail at position i is selected If listboxFruit.Selected(i) Then coll.Add i Stop If Next i Set GetSelectedRows = coll End Role
Reading Data from the VBA Listbox
To read data from the ListBox nosotros can use the ListBox.Value property. This only works when the ListBox is set to simply select one item i.e. MultiSelect is set to frmMultiSelectSingle(see the section VBA ListBox MultiSelect below for more about this).
Unmarried selection just with i column
When but one item is selected we can utilize the Value holding to become the currently selected item:
Dim fruit As String fruit = ListBox1.Value
Continue in mind that if there are multiple columns, Value will merely return the value in the commencement column.
Unmarried choice simply with multiple columns
If the ListBox has Multiple columns y'all can use the Value belongings to get the value in the first cavalcade. You need to read through the Listing belongings to get the values in the other column(due south). The Listing property is substantially an assortment so yous tin care for it like one.
In the case below we read through the columns of row ane(the index of row 1 is 0):
With ListBox1 For j = LBound(.List, 2) To UBound(.List, 2) ' Impress the columns of the first row to the Firsthand Window Debug.Print .List(0, j) Next j End With
Normally you want to print the values in the selected row. You can utilise the ListIndex property to get the selected particular(Annotation that ListIndex returns the last selected items and then it won't work where there are multiple items selected):
' ExcelMacroMastery.com Sub ReadValuesFromSelectedRow() ' Write contents of the row to the Immediate Window(Ctrl Yard) With ListBox1 For j = LBound(.Listing, 2) To UBound(.List, 2) ' Print the columns of the selected row to the Immediate WindowDebug.Print .List(.ListIndex, j) Side by side j End With Cease Sub Multiple selections
If the ListBox has multiple selections and you desire to go all the data from each and then you can use the GetSelectedRows() sub from the section VBA ListBox Selected Items. This will get a collection of all selected rows. You tin use this to impress the information from the selected rows:
Sub PrintMultiSelectedRows() ' Get all the selected rows Dim selectedRows As Collection Set selectedRows = GetSelectedRows(Me.ListBox1) Dim i As Long, j Every bit Long, currentRow As Long ' Read through the selected rows For i = ane To selectedRows.Count With ListBox1 ' Get the current row currentRow = selectedRows(i) ' Print row header Debug.Print vbNewLine & "Row : " & currentRow ' Read items in the current row For j = LBound(.List, 2) To UBound(ListBox1.Listing, two) ' Print the columns of the starting time row to the Immediate Window Debug.Print .List(currentRow, j) Next j End With Side by side i End Sub Office GetSelectedRows(currentListbox As MSForms.ListBox) As Collection ' Create the collection Dim coll As New Collection ' Read through each detail in the listbox Dim i As Long For i = 0 To currentListbox.ListCount - i ' Cheque if detail at position i is selected If currentListbox.Selected(i) Then coll.Add together i End If Next i Set GetSelectedRows = coll End Office
VBA ListBox MultiSelect
We can apply the MultiSelect holding of the ListBox to allow the user to select either a single item or multiple items:
There are 3 selections:
- 0 = frmMultiSelectSingle – [Default]Multiple selection isn't immune.
- ane = frmMultiSelectMulti – Multiple items are selected or deselected by choosing them with the mouse or by pressing the Spacebar.
- two = frmMultiSelectExtended – Multiple items are selected by holding down Shift and choosing them with the mouse, or by property down Shift and pressing an pointer primal to extend the selection from the previously selected item to the electric current particular. You tin can also select items by dragging with the mouse. Holding downward Ctrl and choosing an item selects or deselects that item.
VBA ListBox Columns
You can accept multiple columns in a ListBox. For example, you can load a Range or two-dimensional array to a ListBox using List or RowSource.
Oft when you load information with multiple columns only 1 cavalcade appears. This can be very confusing when you lot are using the Listbox. To get the columns to appear yous take to gear up the ColumnCount belongings to the number of Columns.
Yous should likewise make sure that the ColumnWidths belongings is correct or one of the columns may non announced.
Yous can do it like this:
With listboxFruit .RowSource = "Sheet1!A2:B4" .ColumnCount = 2 .ColumnWidths = "100,100" Cease With
In a existent-world application, you could prepare the RowSource and ColumnCount properties similar this:
With listboxFruit .RowSource = myRange.Accost(External:=True) .ColumnCount = myRange.Columns.Count End With
Come across the AddItem section for how to add data to the other columns when yous are using the AddItem property.
VBA ListBox Column Headers
Cavalcade Headers are some other confusing element of the ListBox. If you lot use the RowSource property to add data to the ListBox and then the line in a higher place the Range volition be automatically used as the header.
For the Column headers to appear the ColumnHeadsproperty must be set to true. You can practise this in the properties window of the ListBox or in the code list this:
ListBox1.ColumnHeads = True If you apply the List or AddItem property to fill the ListBox then the column headers are non available. The best solution, albeit a frustrating ane, is to use labels to a higher place the ListBox. I know information technology sounds crazy but that unfortunately is the reality. The i advantage is that you tin use the Characterization click outcome which is useful if you lot plan to sort the information by a cavalcade.
Creating a ListBox Dynamically
Controls are normally created at blueprint time just you lot tin also create them dynamically at run time:
Dim myListbox Every bit MSForms.ListBox Prepare myListbox = Controls.Add("Forms.ListBox.1") If you want to add an effect to a dynamic command you tin can do information technology like this:
- First of all create a Class similar this:
Public WithEvents myListBox Every bit MSForms.ListBox Individual Sub myListBox_Change() MsgBox "Selection inverse" Finish Sub
- Name the class clsListBoxEvents.Create a variable of this grade object in the UserForm like this:
Private listBoxEvents As New clsListBoxEvents
- Attach the events to the ListBox:
Sub CreateDynamicListBox() ' Create the ListBox Dim newListBox Every bit MSForms.ListBox Set newListBox = Controls.Add("Forms.ListBox.1") ' Add together some items newListBox.List = Array("Apple", "Orange", "Pear") ' Connect the ListBox to the ListBox events grade Set listBoxEvents.myListBox = newListBox End Sub
Notation that you can adhere events to any ListBox. It doesn't have to be created dynamically to practice this.
Loop through ListBoxes
If you want to loop through all the ListBoxes on a UserForm yous tin practice information technology like this:
Dim ctrl As Variant For Each ctrl In Me.Controls If TypeName(ctrl) = "ListBox" Then Debug.Impress ctrl.Name End If Adjacent ctrl
YouTube Video
Check out this video where I use the ListBox. The source code for the video is available from here
What'southward Side by side?
Complimentary VBA Tutorial If yous are new to VBA or you want to sharpen your existing VBA skills then why not try out this Free VBA Tutorial.
Related Training: Go total admission to the Excel VBA preparation webinars and all the tutorials.
(Notation: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
Source: https://excelmacromastery.com/vba-listbox/
Posted by: moorebobtly.blogspot.com
0 Response to "Double-clicking On The Listbox Control Registers Which Default Event?"
Post a Comment