This is a bit better
Option Explicit
Private Sub ComboBox1_Click()
LoadCombo2
LoadCombo3
End Sub
Private Sub ComboBox2_Click()
LoadCombo1
LoadCombo3
End Sub
Private Sub ComboBox3_Click()
LoadCombo1
LoadCombo2
End Sub
Public Sub LoadCombo1()
With Me
.ComboBox1.Clear
CheckItem .ComboBox1, "Bob"
CheckItem .ComboBox1, "Lynee"
CheckItem .ComboBox1, "Amy"
CheckItem .ComboBox1, "Hannah"
End With
End Sub
Public Sub LoadCombo2()
With Me
.ComboBox2.Clear
CheckItem .ComboBox2, "Bob"
CheckItem .ComboBox2, "Lynee"
CheckItem .ComboBox2, "Ames"
CheckItem .ComboBox2, "Hannah"
End With
End Sub
Public Sub CheckItem(This, Item)
Dim ctl As MSForms.Control
Dim SavedItem
With This
If .ListIndex < -1 Then
SavedItem = .Value
Else
SavedItem = Empty
End If
.ListIndex = -1
For Each ctl In .Parent.Controls
If TypeName(ctl) = "ComboBox" Then
If ctl.Name < This.Name Then
If ctl.Value = Item Then
Else
.AddItem Item
End If
Exit Sub
End If
End If
Next ctl
If Not IsEmpty(SavedItem) Then
.Value = SavedItem
End If
End With
End Sub
Private Sub UserForm_activate()
LoadCombo1
LoadCombo2
End Sub
--
HTH
RP
"Tim" wrote in message
...
Thank you both.
Bob, you are right, I think Frank's suggestion was not quite what i was
looking for, but it has started me off with ideas for another project!
that's why i love reading this news group... so much useful info!
I haven't had chance to try out your solution yet, but i think i can see
it
should do what i need... if i manage to improve / tailor it at all i'll
let
you know.
Many thanks
tim
"Bob Phillips" wrote in message
...
I think this is something different Frank.
Tim,
This is tricky as you presumably need to put it back if the selection
that
removed it is changed? On that basis the best I could come up with is
this
kludge. I am sure it can be improved upon, it loses the selection when a
box
is re-loaded so that needs correcting, but it might get you started.
Private Sub ComboBox1_Click()
LoadCombo2
End Sub
Public Sub LoadCombo1()
With Me
.ComboBox1.Clear
CheckItem .ComboBox1, "Bob"
CheckItem .ComboBox1, "Lynee"
CheckItem .ComboBox1, "Amy"
CheckItem .ComboBox1, "Hannah"
End With
End Sub
Public Sub LoadCombo2()
With Me
.ComboBox2.Clear
CheckItem .ComboBox2, "Bob"
CheckItem .ComboBox2, "Lynee"
CheckItem .ComboBox2, "Ames"
CheckItem .ComboBox2, "Hannah"
End With
End Sub
Public Sub CheckItem(This As ComboBox, Item)
Dim ctl As MSForms.Control
Dim SavedListIndex As Long
With Me
SavedListIndex = This.ListIndex
This.ListIndex = -1
For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
If ctl.Name < This.Name Then
If ctl.Value = Item Then
Exit Sub
Else
This.AddItem Item
End If
End If
End If
Next ctl
This.ListIndex = SavedListIndex
End With
End Sub
Private Sub UserForm_activate()
LoadCombo1
LoadCombo2
End Sub
--
HTH
RP
"Frank Kabel" wrote in message
...
Hi
as a starting point:
http://www.xldynamic.com/source/xld.Dropdowns.html
"Tim" wrote:
i have a userform with four comboboxes from which the user makes
their
choice (then for each combobox, some extra info is completed in
adjacent
textboxes).
all comboboxes could potentially start with the same list of
data, but what i want to do is remove the list item from an unused
combobox
if it has already been selected in another combobox previously (but
then
if
that first one changes, the item then becomes available again in the
remaining comboboxes). ie, if the complete list contains five items
and
one
is chosen in one of the comboboxes, the remaining 'boxes have only
four
(and
so on until all the boxes have selections made).
so the question is, how can i make it 'look up' the already selected
items
and remove it from the
remaining combobox lists, then add it back if the combobox choice
changes?
thank you.