View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Leonard Lan Leonard Lan is offline
external usenet poster
 
Posts: 8
Default Use match function to add different item in Combobox

Hello,
I copied the following codes, but duplicated item is added into the list.
Could anyone help me find out reason? Thank you.

When you type a value that is not already in the list in the ComboBox
control, you may want to add the new value to the list. To add the new value
that you typed in the ComboBox control if the ComboBox control is not bound
to the worksheet, follow these steps:
Start Excel, and then open a new blank workbook.
On the Tools menu, point to Macro, and then click Visual Basic Editor.
On the Insert menu, click UserForm to insert a UserForm in your workbook.
Add a ComboBox control to the UserForm.
On the Insert menu, click Module to insert a module sheet.
In the Code window, type the following code:
Sub PopulateComboBox()

Dim MyArray As Variant
Dim Ctr As Integer
MyArray = Array("Apples", "Oranges", "Peaches", "Bananas", "Pineapples")

For Ctr = LBound(MyArray) To Ubound(MyArray)
UserForm1.ComboBox1.AddItem MyArray(Ctr)
Next

UserForm1.Show

End Sub

Add a CommandButton control to the UserForm.
Double-click the CommandButton control to display the Code window for the
CommandButton control.
In the Code window, type the following code for the CommandButton1 Click
event:
Private Sub CommandButton1_Click()

Dim listvar As Variant

listvar = ComboBox1.List

On Error Resume Next
' If the item is not found in the list...
If IsError(WorksheetFunction.Match(ComboBox1.Value, listvar, 0)) Then
' add the new value to the list.
ComboBox1.AddItem ComboBox1.Value
End If

End Sub

On the Tools menu, click Macros, click PopulateListBox, and then click Run.

The ComboBox control is populated, and then the UserForm appears.
In the ComboBox control, type Apples, clike Commandbutton, another "Apples"
is added. Why?