Using Multiple Select List Boxes in a Custom Dialog Box

ID: Q97486

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:

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

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}).

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.

Type the following items in the macro sheet:


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

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.

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.

When you run the macro, it will put the items you selected from the multiple list box in cell K1 (under the cell cell titled "YOU SELECTED"). The selected items are stored under the defined name "array" (to see this defined name, choose Define Name from the Formula menu--you should see something similar to ={1,2,4} in the Refers To box, indicating that you picked the first, second, and fourth items in the list).

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

Additional query words: HOWTO custom select 4.00a

Keywords :
Version : WINDOWS:3.0,4.0,4.0a; OS/2:3.0; MACINTOSH:3.0,4.0
Platform : MACINTOSH OS/2 WINDOWS
Issue type :