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
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