Read value from combobox (cleaned off duplications)
 

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, befo=j
NoDupes.Add Swap2, befo=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

--
sven_da
-----------------------------------------------------------------------
sven_dau's Profile: http://www.excelforum.com/member.php...fo&userid=3529
View this thread: http://www.excelforum.com/showthread.php?threadid=55079

Read value from combobox (cleaned off duplications)
 

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?



sven_dau wrote:

I list items from a separate worksheet in a combobox (without showing
duplications). I want to show the selected value in a cell in the same
worksheet with the linked cell option in properties.
All runs well: I see the items, select one and for a glimpse of a
second I see it in the cell before it disappears. Also, the selected
value is not shown in the combobox itself after clicked upon. Any
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, befo=j
NoDupes.Add Swap2, befo=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 Sub

--
sven_dau
------------------------------------------------------------------------
sven_dau's Profile: http://www.excelforum.com/member.php...o&userid=35291
View this thread: http://www.excelforum.com/showthread...hreadid=550795

--

Dave Peterson