XL5: List in Dialog Box Does Not Reflect Changes Made by Macro

ID: Q111900

5.00 5.00c WINDOWS kbprb

The information in this article applies to:

- Microsoft Excel for Windows, versions 5.0, 5.0c

SYMPTOMS

In Microsoft Excel, when you use a Visual Basic macro to clear (deselect) an item in a list box, if the list box has a selection type of Multi or Extend, the item may appear to remain selected.

Note that Microsoft Excel DOES recognize that the item is no longer selected; this problem has to do with the way that the dialog box is displayed.

CAUSE

If you use a Visual Basic macro to clear an item in a list box while the dialog box that contains that list box is active, the screen may not be properly redrawn (to show that the item is no longer selected).

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows 95, version 7.0.

MORE INFORMATION

In custom dialog boxes, if a list box has the Selection option set to Multi, you can choose any number of items from the list. For example, if a list contains Alpha, Bravo, and Charlie, you can select any, none, or all of those items.

You can use a Visual Basic macro to select and clear items in a multiple selection list box by changing the Selected property of a single list item. For example, if the active dialog box contains a list box (for example, List1) that contains three items (for example, Alpha, Bravo, Charlie), you can select the first item by using this line of code:

   ActiveDialog.ListBoxes("List1").Selected(1) = True

To clear (deselect) the third item, use this code:

   ActiveDialog.ListBoxes("List1").Selected(3) = False

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."

The following example assumes you have a workbook that contains a worksheet (Sheet1), a dialog sheet (Dialog1), and a Visual Basic module (Module1). The worksheet contains the following values:

   A1: Alpha
   A2: Bravo
   A3: Charlie
   A4: Delta

The dialog sheet contains an OK button that is set to dismiss the dialog box, another button (Button1), and a Multi Selection list box (List1) that is linked to Sheet1!$A$1:$A$4.

To create Button1:

1. On the Forms toolbar, choose Create Button to create a

   button in your dialog sheet.

2. Select the button.

3. In the name box, type "Button1" (without the quotation marks) and press

   the ENTER key.

4. From the Tools menu, choose Assign Macro.

5. In the Assign Macro dialog box, select the ClearItem subroutine

   (shown below) and choose OK.

To create a Multi Selection list box and the necessary link:

1. Use the List Box button to create a list box in your dialog sheet.

2. Select the list box.

3. In the name box, type "List1" (without the quotation marks) and press

   ENTER.

4. From the Format menu, choose Object.

5. Select the Control tab.

6. In the Input Range box, type "Sheet1!$A$1:$A$4" (without the

   quotation marks).

7. Under Selection Type, select Multi.

8. Choose OK to accept the change.

The list box is now linked to Sheet1!$A$1:$A$4 and is a multiple selection list box.

In Module1, enter the following subroutine:

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

Sub ShowDialog()

   'Dimension some variables.
   Dim CurList As Variant, LTemp As Variant, LItem As Variant
   Dim Counter As Integer

   'Show the dialog box.
   DialogSheets("Dialog1").Show

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

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

   'Initialize the Counter variable.
   Counter = 1

   'Iterate through the loop once for each item in the array (which is
   'the same as iterating once for each item in the list box).
   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.
         'CurList.List(Counter) gets us the value of the selected item
         '("Alpha", "Bravo", etc.).
         MsgBox CurList.List(Counter) & " is selected."

      'Otherwise...
      Else

         '...indicate that it isn't selected.
         MsgBox CurList.List(Counter) & " is NOT selected."
      End If

      'Increment the Counter so we can get the value of the next
      'selected item.
      Counter = Counter + 1
   Next                                  'repeat until all done
End Sub

Sub ClearItem()

   'Clear the second item in the list box.
   ActiveDialog.ListBoxes("List1").Selected(2) = False
End Sub
'--------------------------------------------------------------------

To test the subroutine, position the insertion point in the line that contains Sub ShowDialog(), and either press F5 or choose Start from the Run menu, and do the following:

1. While the dialog box is visible, select the third and second items in

   the list, in that order.

2. Choose the Button1 button. Note that items 2 and 3 both appear to be
   selected.

3. Choose the OK button.

   Note that the message boxes indicate that the second item (Bravo)
   is not selected, even though it appears to be selected in the dialog
   box.

4. Run the ShowDialog subroutine again to redisplay the dialog box.

5. Clear the third item in the list. Then, select the fourth and

   second items, in that order. Then, clear the fourth item.

   At this point, only the second item should appear to be selected.

6. Choose the Button1 button.

   The second item will appear to be cleared, and the fourth item will
   appear to be selected.

7. Choose the OK button.

You will receive message boxes indicating that all items are not selected, even though the fourth item appears to be selected.

In each case, the message boxes show the true status of each item: the way the list appears in the dialog box does not accurately represent the status of the items in the list.

KBCategory: kbprb KBSubcategory:

Additional reference words: 5.00

Version           : 5.00 5.00c
Platform          : WINDOWS

Last Reviewed: September 14, 1996