XL: RemoveItem and RemoveAllItems Methods May Not Work

ID: Q105877

The information in this article applies to:

Microsoft Excel for Windows, version 5.0
Microsoft Excel for the Macintosh, version 5.0
Microsoft Excel for Windows 95, version 7.0

SUMMARY

In Microsoft Excel, the RemoveItem and RemoveAllItems methods cannot be used to remove items from a list box if the list box is linked to a range on a worksheet or a Microsoft Excel 4.0 macro sheet.

This is by design in Microsoft Excel.

MORE INFORMATION

In Microsoft Excel, when you use a Microsoft Visual Basic for Applications module to create a list box, there are two ways you can add items to the list:

Use the AddItem method to add items to the list box.
-or-
Do the following to link a worksheet or a Microsoft Excel 4.0 macro sheet range to the list box by setting an input range for the list box.
1. In the dialog sheet, select the list box.
2. On the Format menu, click Object.
3. Click the Control tab.
4. In the Input Range edit box, enter the range where your list
```
      items are stored (for example, Sheet1!$A$1:$A$10).
```
5. Click OK to accept the change.

If your list box is linked to a worksheet or a Microsoft Excel 4.0 macro sheet range, you cannot use the RemoveItem or RemoveAllItems methods to remove items from the list. Attempting to do so will result in the following error message:

   RemoveItem method of ListBox class failed

Visual Basic Code Example

Microsoft provides programming examples 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 article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

This Visual Basic code example displays a dialog box. When you select one button, it hides the dialog box, removes the first item from the list in the dialog box, and redisplays the dialog box. Another button ends the procedure.

You need to use this example ONLY if your list box is linked to cells on a worksheet or a Microsoft Excel 4.0 macro sheet. Lists created using AddItem can use the RemoveItem and RemoveAllItems methods to remove items from a list.

This example assumes that you have a dialog sheet (Dialog1) and a worksheet (Sheet1) both contained in the same workbook. The dialog sheet contains two buttons, DoneButton and RemoveButton, and a list box, List Box 1. The worksheet contains a list of items in cells $A$1:$A$10. The list box has its Input Range set to Sheet1!$A$1:$A$10.

To run the example, click anywhere on the line that reads "Sub MainMacro()" and either press the F5 key or click Start on the Run menu.

   '-----------------------------------------------------------
   Public DoneFlag As Integer

   Sub MainMacro()

      DoneFlag = 0                         ' Initialize DoneFlag.

      ' While the DoneFlag does not equal 1 (which will only occur if the
      ' DoneButton is clicked), continue to loop through the Subroutine.

      Do
         DialogSheets("Dialog1").Show      ' Display the dialog box.
      Loop Until DoneFlag = 1              ' Loop until DoneButton clicked.

   End Sub

   Sub RemoveButton_Click

      DoneFlag = 0                          ' Ensure DoneFlag set to 0.
      DialogSheets("Dialog1").Hide          ' Hide the dialog box.

      '  The following line deletes cell A1 ["Cells(1, 1)"] from worksheet
      '  Sheet1.

      Worksheets("Sheet1").Cells(1, 1).Delete

   End Sub

   Sub DoneButton_Click

      DoneFlag = 1                          ' Set the DoneFlag.

   End Sub
'-----------------------------------------------------------

When either of the DoneButton or RemoveButton buttons are activated, the appropriate subroutines (DoneButton_Click or RemoveButton_Click) are run: in the RemoveButton subroutine, an item is deleted from the list, which is automatically updated, and in the DoneButton subroutine, a flag which indicates that you want to end the macro is set.

The MainMacro subroutine then resumes and proceeds to either loop back upon itself if DoneFlag equals 0 (if the RemoveButton was activated) or exit the loop and end the macro (if the DoneButton was activated).

Note that this example uses the Delete method to eliminate a cell from the list. If you want to redefine the list without deleting the cell, you will need to use the ListFillRange property to determine the proper range to use for your list.

Note also that when you use this method to remove items from a list, you must hide and reshow the dialog box in order for the list to appear correctly on the screen. This is accomplished by using the DialogSheets("Dialog1").Hide command in the RemoveButton_Click subroutine.

Additional query words: 5.00 7.00

Keywords          : kbprg PgmHowto 
Version           : WINDOWS:5.0,7.0; MACINTOSH:5.0
Platform          : MACINTOSH WINDOWS

Last Reviewed: May 17, 1999