Using Multiple Select List Boxes in a Custom Dialog Box

The information in this article applies to:

• Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a
• Microsoft Excel for OS/2, version 3.0
• Microsoft Excel for the Macintosh, versions 3.0, 4.0

SUMMARY

In Microsoft Excel, you can use a standard list box to create a multiple selection list box in a custom dialog box. To select multiple contiguous items from the list box, hold down the SHIFT key. To make non-contiguous selections hold down CTRL in Microsoft Excel for Windows or COMMAND in Microsoft Excel for the Macintosh. The results of your choices will be stored as an array.

To access more than one item from a list box, type a name in the Init/Result column for the list box line (item number 15) in the dialog definition. If the Init/Result column contains a name, the list box becomes a multiple-selection list box.

NOTE: In versions of Microsoft Excel prior to 3.0 you can select only one item from a custom list box.

MORE INFORMATION

To create a multiple selection list box:

1. From the File menu, choose New and select the Macro Sheet option.

2. Enter the following dialog definition table into the macro sheet:

         B1:Item#  C1:X   D1:Y   E1:Width F1:Height G1:Text      H1:Result
         B2:       C2:    D2:    E2:298   F2:  154  G2:Food List H2:
         B3:15     C3:10  D3:6   E3:276   F3:  110  G3:list      H3:array
         B4:1      C4:200 D4:123 E4:88    F4:  28   G4:OK        H4:
   
      NOTE: If the name "array" (cell H3) has not been previously defined,
      Microsoft Excel creates the name when the dialog box is opened. The
      name must refer to a single number or a one-dimensional array of
      numbers (vertical or horizontal array) corresponding to the positions
      in the list of the initially selected values (such as, ={1,4,10}).
   
   

3. Select the range B2:H4, and choose Define Name from the Formula menu. In the Name box, type "dialog" (without the quotation marks) and choose the OK button.

4. Type the following items in the macro sheet:

         J1: LIST            K1: YOU SELECTED:
         J2: apples
         J3: bananas
         J4: carrots
   
   

5. Select cells J2:J4 and choose Define Name from the Formula menu. In the name box, type "list" (without the quotation marks), and choose the OK button.

6. Type the following macro:

         A1:=CLEAR(SELECT(K2:K4))
         A2 =DIALOG.BOX(dialog)
         A3:=FOR("counter",1,COUNT(array))
         A4:=INDEX(array,counter)
         A5:=FORMULA(OFFSET(J1,A4,0),OFFSET(K1,COUNTER,0))
         A6:=NEXT()
         A7:=RETURN()
   

   Note that in the above macro:

       - Line 1 clears the previously selected items.
   

       - Line 2 calls the dialog box.
   

       - Line 3 starts a loop with the counter initially set to 1, and
         loops as many times as the number of items you chose.
   

       - Line 4 uses the index and counter arguments to find the next
         choice.
   

       - Line 5 uses the number returned in A3 to select the text
         equivalent in LIST, and then displays it in the YOU SELECTED box
         (in cell K1).
   

       - Line 6 increments the FOR loop.
   

       - Line 7 ends the macro.
   

Each time you run the dialog box, the items you selected the last time you ran the dialog box will be displayed as the selected items. To set the default for the selected items to the first item in the list, add the following line to your macro before the =DIALOG.BOX(DIALOG) line:

   =SET.NAME("array",1)

REFERENCES

"User's Guide 2," version 4.0, page 276-277 "User's Guide," version 3.0, page 636-637