Read value from combobox (cleaned off duplications)

[sven_dau]

I list items from a separate worksheet in a combobox (without showin
duplications). I want to show the selected value in a cell in the sam
worksheet with the linked cell option in properties.
All runs well: I see the items, select one and for a glimpse of
second I see it in the cell before it disappears. Also, the selecte
value is not shown in the combobox itself after clicked upon. An
ideas?

Here is the code:

Private Sub stv2005_Change()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item
Me.stv2005.Clear
Set AllCells = Worksheets("NKADaten").Range("C4:C200")
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i
For Each Item In NoDupes
Me.stv2005.AddItem Item
Next Item
End Su

 

[Dave Peterson]

You're rebuilding the list each time the combobox changes.

Shouldn't you just build the list once--when the worksheet is activated or when
the workbook opens (or maybe if that range (NKADaten!C4:C200) changes?