combobox help
 

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.

combobox help
 

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.

combobox help
 

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.

combobox help
 

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.

combobox help
 

I might play with it myself. Will post back if I improve it, so check it out
later.

--

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.

combobox help
 

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.