Microsoft KB Archive/126794

= VB Code for Working with List Box Items in a Dialog Box =

Article ID: 126794

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q126794



SUMMARY
In Microsoft Excel, you can use the list box control on a dialog sheet to present a user with a list of items that can be scrolled and selected. You can use Visual Basic, Applications Edition code to add items to (populate) this list and to retrieve items from it.

The following Visual Basic macro code examples demonstrate a few of the more common tasks that you may want to perform when you add items to or retrieve data from a list box.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

To populate a list box with data from cells on a worksheet
Sub Example1 DialogSheets(1).ListBoxes(1).ListFillRange = "MyWorksheet!A2:A15" End Sub NOTE: "MyWorksheet" is the name of the worksheet that contains the data you would like placed in the list box.

To populate a list box using an array
Sub Example2 DialogSheets(1).ListBoxes(1).List = _ Array("Mon", "Tue", "Wed", "Thu", "Fri") End Sub

To populate the list box with data from a horizontal array of cells
Ordinarily, list boxes are populated with a column of data. If you need to populate a list box with a row of data, use this code: Sub Example3 DialogSheets(1).ListBoxes(1).List = _ Worksheets("Sheet1").Range("A1:F1") End Sub NOTE: "Sheet1" is the worksheet that contains your data.

To clear all items in a list box with RemoveAllItems
Sub Example4 DialogSheets(1).ListBoxes(1).RemoveAllItems End Sub For additional information, please see the following article in the Microsoft Knowledge Base:

105877 XL: RemoveItem and RemoveAllItems Methods May Not Work

To retrieve the selected item in a single-select list box
Sub Example6

Dim LTemp As Variant Dim LItem As Variant Dim Counter As Integer Dim CurList as ListBox

' Set an object name for easy referencing of the list box. Set CurList = DialogSheets(1).ListBoxes(1)

' Put the Selected array into the variable LTemp. LTemp = CurList.Selected

' Initialize a Counter variable. Counter = 1

' Iterate through the loop once for each item in the array. For Each LItem In LTemp

' If the value of the current item is True... If LItem = True Then

' Show a message box indicating the item is selected. MsgBox CurList.List(Counter) & " is selected."

End If

' Increment the Counter to get next selected item. Counter = Counter + 1 Next

End Sub For additional information, please see the following article in the Microsoft Knowledge Base:

124214 XL: Returning a Value from a List Box in Visual Basic

To retrieve the selected items from a multi-select list box
Sub Example6

Dim LTemp As Variant Dim LItem As Variant Dim Counter As Integer Dim CurList as ListBox

' Set an object name for easy referencing of the list box. Set CurList = DialogSheets(1).ListBoxes(1)

' Put the Selected array into the variable LTemp. LTemp = CurList.Selected

' Initialize a Counter variable. Counter = 1

' Iterate through the loop once for each item in the array. For Each LItem In LTemp

' If the value of the current item is True... If LItem = True Then

' Show a message box indicating the item is selected. MsgBox CurList.List(Counter) & " is selected."

End If

' Increment the Counter to get next selected item. Counter = Counter + 1 Next

End Sub For additional information, please see the following article in the Microsoft Knowledge Base:

111564 XL: Determining Which Items Are Selected in a List Box

To retrieve all items in a list box using a For-Each statement
Sub Example7

Dim mtemp As Object Dim myList Dim LItem As Variant

' Set mtemp as a ListBox object. Set mtemp = DialogSheets(1).ListBoxes(1)

' Set mtemp = myList. myList = mtemp.List

' Create a For-Each Loop. For Each LItem In myList

' Display the selected item. MsgBox Litem

Next

End Sub

