Article ID: 105877
Article Last Modified on 10/11/2006
APPLIES TO
- Microsoft Excel 95 Standard Edition
- Microsoft Excel 5.0 Standard Edition
- Microsoft Excel 5.0 for Macintosh
This article was previously published under Q105877
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.
- In the dialog sheet, select the list box.
- On the Format menu, click Object.
- Click the Control tab.
- In the Input Range edit box, enter the range where your list items are stored (for example, Sheet1!$A$1:$A$10).
- 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:
Visual Basic Code Example
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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 XL
Keywords: kbprogramming KB105877
