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?

Use match function to add different item in Combobox
 

You would almost certainly get a n answer quicker if you posed in the
Programming area instead of the Functions area. Ok, it looks like you are
adding elements from the array into your form with this:
ComboBox1.AddItem ComboBox1.Value

Try something like this:
Right €˜Sheet1 and click View Code and paste this in:
Private Sub CommandButton1_Click()
ActiveWindow.WindowState = xlMinimized
UserForm1.Show
End Sub

Add a UserForm and add a ComboBox; click RowSource and add this:
=Sheet1!Z2:Z5
Put your fruit names in this Array

Add a CommandButton, double-click the Button ad add this code:
Private Sub CommandButton1_Click()
On Error Resume Next
Sheets("Sheet1").Activate

ActiveCell = ComboBox1.Text
ActiveCell.Offset(1, 0).Select

Unload UserForm1

On Error Resume Next
ActiveWindow.WindowState = xlMaximized
End Sub

HTH,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Leonard Lan" wrote:

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?

Use match function to add different item in Combobox
 

Thanks for you help. But your code looks not quite related to my questions.
What I want to do is to add a new item in Combobox itself, not through sheet
cells. And only different items are added.

One more thing, how do I keep the added items in Combobox after the form is
closed and reopened? Is there any function or property that can do it?

I appreciate you a lot!

"ryguy7272" wrote:

You would almost certainly get a n answer quicker if you posed in the
Programming area instead of the Functions area. Ok, it looks like you are
adding elements from the array into your form with this:
ComboBox1.AddItem ComboBox1.Value

Try something like this:
Right €˜Sheet1 and click View Code and paste this in:
Private Sub CommandButton1_Click()
ActiveWindow.WindowState = xlMinimized
UserForm1.Show
End Sub

Add a UserForm and add a ComboBox; click RowSource and add this:
=Sheet1!Z2:Z5
Put your fruit names in this Array

Add a CommandButton, double-click the Button ad add this code:
Private Sub CommandButton1_Click()
On Error Resume Next
Sheets("Sheet1").Activate

ActiveCell = ComboBox1.Text
ActiveCell.Offset(1, 0).Select

Unload UserForm1

On Error Resume Next
ActiveWindow.WindowState = xlMaximized
End Sub

HTH,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Leonard Lan" wrote:

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?