LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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?
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
adding totals on item number match still_learning New Users to Excel 6 May 21st 08 01:21 PM
match item in 2 excel files [email protected] Excel Discussion (Misc queries) 0 April 25th 08 03:38 AM
item limit for a combobox? KarenH Excel Worksheet Functions 3 July 20th 06 09:30 PM
Match return #NA ...though item exists! Sige Excel Discussion (Misc queries) 2 January 12th 06 02:49 PM
does item match any in column B MatthewTap Excel Discussion (Misc queries) 3 October 5th 05 09:31 PM


All times are GMT +1. The time now is 11:40 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"