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

• 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 

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

Visual Basic Code Example

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

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

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



7. Under Selection Type, select Multi.



8. Choose OK to accept the change.

'------------------------------------------------------------------
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
'-------------------------------------------------------------------- 

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.

Additional query words:

Keywords :
Version : 5.00 5.00c
Platform : WINDOWS
Issue type :