XL: VB Code for Working with List Box Items in a Dialog Box

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, version 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

SAMPLE VISUAL BASIC CODE TO ADD ITEMS TO A LIST

To populate a list box with data from cells on a worksheet

   Sub Example1()
      DialogSheets(1).ListBoxes(1).ListFillRange = "MyWorksheet!A2:A15"
   End Sub

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

SAMPLE VISUAL BASIC CODE TO REMOVE ITEMS FROM A LIST

To clear all items in a list box with RemoveAllItems

   Sub Example4()
      DialogSheets(1).ListBoxes(1).RemoveAllItems
   End Sub

   ARTICLE-ID: Q105877
   TITLE     : XL: RemoveItem and RemoveAllItems Methods May Not Work

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

   Sub Example5()

      Dim theContents As String

      ' Define a With statement to optimize code.
      With DialogSheets(1).ListBoxes(1)

         ' Define theContents equal to the selected item.
         theContents = .List(.ListIndex)

         ' End the With Statement.
      End With

      ' Display the selected item.
      MsgBox theContents

   End Sub

   ARTICLE-ID: Q124214
   TITLE     : 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

   ARTICLE-ID: Q111564
   TITLE     : 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

REFERENCES

"Visual Basic User's Guide," pages 226, 231