'For Each Item in List' Doesn't Work

   For Each mCell In Selection  '"Selection" is the group argument
      MsgBox mCell.Value
   Next
				

WORKAROUND

To avoid this error, set an object equal to the list

   <Object> = <Listname>.List
				

and then use the object in the For Each statement. For example, instead of

     xList = DialogSheets("Dialog1").ListBoxes("List1")
   For Each mItem in xList.List
      <statements>
   Next

use

   xList = DialogSheets("Dialog1").ListBoxes("List1")
   mTemp = xList.List
   For Each mItem in mTemp
      <statements>
   Next
				

Visual Basic Code Example

Microsoft provides examples of Visual Basic procedures 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 Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

This example shows one way that you can avoid the error associated with calling a list directly from a For Each statement.

The following example assumes you have a workbook that contains a Visual Basic module (Module1) and a dialog sheet (Dialog1). The dialog sheet contains a single list box (List1).

In Module1, enter the following subroutine:

'----------------------------------------------------------------------
Option Explicit

Sub ForEachListItem()

   'Dimension some variables.
   Dim Alpha As Variant, Foxtrot As Variant, Golf As Variant

   'Set an object name for easy referencing of the list box.
   Set Alpha = DialogSheets("Dialog1").ListBoxes("List1")

   'Add three items to the list.
   Alpha.AddItem "Bravo"
   Alpha.AddItem "Charlie"
   Alpha.AddItem "Delta"

   'Set an object name so that the For Each structure can function
   'properly.
   Golf = Alpha.List

   'Iterate through the loop once for each item in Golf (which is
   'the same as iterating once for each item in the list box).
   For Each Foxtrot In Golf

      'Show the current list item in a message box.
      MsgBox Foxtrot
   Next                                  'repeat until all done
End Sub
'----------------------------------------------------------------------
				

To run the subroutine, position the cursor in the line that reads "Sub ForEachListItem()," and either press F5 or choose Start from the Run menu.

If the For Each line is entered as shown, the subroutine will run properly and message boxes will display the names of the items in the list.

If the For Each line is altered to refer to Alpha.List directly, without using an intermediary function (in this case, Golf), you will receive the error message shown above and the message boxes will not be displayed.