I have created a userform (frmDatEntr) in my workbook with three
comboboxes (cbo1 - cbo3 inclusive) which act as input boxes for three
different ranges of 96 cells in the sheet. I am using a version of J.
Walkenbach's code to generate a unique list of values to create the
combobox lists which are updated each time a commandbutton is fired and
which copies the combobox value into the specified range. The code
works well generating and updating my unique list until I pull up a
previously entered value and on firing the command button the value in
the combobox disappears from view. Unfortunately this wastes a users
time in having to select the value once again.
Here is the code for one of the comboboxes to generate the unique list.
The variables have all been previously declared.:

'clear existing list
frmDatEntr.cbo1.Clear

'create new list
Set AllCells =
Range("B3:M3,B6:M6,B9:M9,B12:M12,B15:M15,B18:M18,B 21:M21,B24:M24")
On Error Resume Next
For Each Cell In AllCells
If Cell.Value = "" Then
Else
NoDupes.Add Cell.Value, CStr(Cell.Value)
End If
Next Cell

'list unique items
For Each Item In NoDupes
If Item = "" Then
Else
frmDatEntr.cbo1.AddItem (Item)
End If
Next

This code is part of a larger macro which, as mentioned previously, is
run when a commandbutton is clicked. I am using the
frmDatEntr.cbo1.clear to avoid obtaining multiple copies of the entries
in the combobox list, however I think this may be the root of my
problem as without this line the entry stays in view.

Is there a way to clear/update the combobox list without experiencing
this problem? Any thoughts please?
Many thanks in anticipation.
Simon

' get current selection
idx = frmDatEntr.cbo1.Listindex
'clear existing list
frmDatEntr.cbo1.Clear

'create new list
Set AllCells =
Range("B3:M3,B6:M6,B9:M9,B12:M12,B15:M15,B18:M18,B 21:M21,B24:M24")
On Error Resume Next
For Each Cell In AllCells
If Cell.Value = "" Then
Else
NoDupes.Add Cell.Value, CStr(Cell.Value)
End If
Next Cell

'list unique items
For Each Item In NoDupes
If Item = "" Then
Else
frmDatEntr.cbo1.AddItem (Item)
End If
Next
frmDatEntr.cbo1.Listindex = idx

--
Regards,
Tom Ogilvy


" wrote:

I have created a userform (frmDatEntr) in my workbook with three
comboboxes (cbo1 - cbo3 inclusive) which act as input boxes for three
different ranges of 96 cells in the sheet. I am using a version of J.
Walkenbach's code to generate a unique list of values to create the
combobox lists which are updated each time a commandbutton is fired and
which copies the combobox value into the specified range. The code
works well generating and updating my unique list until I pull up a
previously entered value and on firing the command button the value in
the combobox disappears from view. Unfortunately this wastes a users
time in having to select the value once again.
Here is the code for one of the comboboxes to generate the unique list.
The variables have all been previously declared.:

'clear existing list
frmDatEntr.cbo1.Clear

'create new list
Set AllCells =
Range("B3:M3,B6:M6,B9:M9,B12:M12,B15:M15,B18:M18,B 21:M21,B24:M24")
On Error Resume Next
For Each Cell In AllCells
If Cell.Value = "" Then
Else
NoDupes.Add Cell.Value, CStr(Cell.Value)
End If
Next Cell

'list unique items
For Each Item In NoDupes
If Item = "" Then
Else
frmDatEntr.cbo1.AddItem (Item)
End If
Next

This code is part of a larger macro which, as mentioned previously, is
run when a commandbutton is clicked. I am using the
frmDatEntr.cbo1.clear to avoid obtaining multiple copies of the entries
in the combobox list, however I think this may be the root of my
problem as without this line the entry stays in view.

Is there a way to clear/update the combobox list without experiencing
this problem? Any thoughts please?
Many thanks in anticipation.
Simon

Thank you Tom for your swift reply and for helping me understand the
problem.
Your answer worked for me once I had values in the list i.e. the
listindex was =< 0. On adding the first entry the value disappeared
from the combobox on activating the commandbutton i.e. when the
ListIndex was -1. Therefore I got round the problem in this way, with
all variables previously declared:

'combobox1 list sample names

'clear existing list
' get current selection
idx = frmDatEntr.cbo1.ListIndex
MsgBox idx 'gives indication of listindex value for testing only

frmDatEntr.cbo1.Clear

'create new list
Set AllCells =
Range("B3:M3,B6:M6,B9:M9,B12:M12,B15:M15,B18:M18,B 21:M21,B24:M24")
On Error Resume Next
For Each Cell In AllCells
If Cell.Value = "" Then
Else
NoDupes.Add Cell.Value, CStr(Cell.Value)
End If
Next Cell

'list unique items
For Each Item In NoDupes
If Item = "" Then
Else
frmDatEntr.cbo1.AddItem (Item)
End If
Next

NDcnt = NoDupes.Count
If idx = -1 Then
idx = NDcnt - 1 'to take into account listindex starts at 0
End If
frmDatEntr.cbo1.ListIndex = idx

This ensured the ListIndex was never -1 and so always had the most
recent entry displayed in the combobox.

Thank you Tom once again.

Simon