Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
ComboBox | Excel Discussion (Misc queries) | |||
Populating combobox from another combobox | Excel Programming | |||
New to ComboBox | Excel Programming | |||
ComboBox | Excel Programming |