S
simon.q.rice
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,B21: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
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,B21: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