Remove item from combo box once it's been selected

[nokoy]

I have a user form that has a combo box that is populated by a range from a worksheet. All the data collected in the user form are then written to a worksheet called Data. What I would like to do is to exclude all the items that has been selected already. Say for example my Rowsource range contains the ff:

Oranges
apples
grapes
bananas
and so on .....

If the user has already selected and entered Oranges(in cell A1) & apples(in cell A2) to the Data worksheet it will no longer be in the dropdown list of the combo box.

Any help will be greatly appreciated.

Thanks

Nokoy

 

[herpasymplex10]

The Following procedures should all be placed in the userform.
These Procedures:
1:will never load any items into cbx that have already been placed on data sheet
2: never go back to source sheet for refresh of data , i.e. the items are removed from cbx list every time item is added to data sheet, without going back to source sheet.
3: Source fruit list is never deleted or modified


Private Sub UserForm_Initialize()
For Each Fruit In Range("rngFruits")
If Len(Trim(Fruit)) > 0 Then
If Not FindFruitInData(Fruit) Then cbxFruits.AddItem Fruit
End If ' not blank
Next Fruit
End Sub

Function FindFruitInData(Fruit) As Boolean
With Worksheets("Data").Cells
Set FruitLoc = .Find(Fruit, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
If Not FruitLoc Is Nothing Then
FindFruitInData = True
Else
FindFruitInData = False
End If
End With
End Function


Private Sub cmdAddToData_Click()
'add fruit to Data Sheet
With Sheets("Data")
NxRw = .Cells(65536, 1).End(xlUp).Row + 1
.Cells(NxRw, 1).Value = cbxFruits.Value
End With
' Take from combo list
With UserForm1.cbxFruits
.RemoveItem .ListIndex
cbxFruits.Value = ""
End With
End Sub



ASSUMPTIONS:
1: Target Data sheet is called "Data" .. to modify do search for the word "Data" in above procedures
2: On Target sheet fruit are placed in Column 1 ( to modify change the number "1" in (65536, 1). AND NxRw, 1).
3: The source fruit list is found in a user defined range called rngFruits

This (I think) answers an unanswered post I made.
Working on modifying it for my sheet (skaters instead of fruit)

If I wanted this to work for two comoboxes, would I just do this formula twice with a new named range?