Read value from combobox (cleaned off duplications)

S

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
 
D

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top