non-repeating Comboboxes values

N

noname

Hi,

I have 4 comboboxes on a form.

In Form_Initialize, i am adding 4 values to each of them. These values
are same for all the 4 combos.

Dim strEPIC() As String
ReDim strEPIC(4)

strEPIC(0) = "Empathy"
strEPIC(1) = "Persuasion"
strEPIC(2) = "Impact"
strEPIC(3) = "Communication"

For i = 0 To 3
Me.ComboBox1.AddItem strEPIC(i)

Me.ComboBox2.AddItem strEPIC(i)

Me.ComboBox3.AddItem strEPIC(i)

Next i



So my question is:
--------------------------
If i select a value in the 1st combo, the remaining 3 should not show
the value and only allow user to select the remaining 3 values in the
other combos. similarly, if i select a value in 2nd combo, the
remaining 2 combos should not show the selected items in the previous
2 combos i.e. Exclude those values... So each combobox should
eventually have only 1 unique value selected in it and should show
remaining 3 items in its list. e.g.

combobox 1
-----------------
selected value: Empathy
remaining items in it: Persuasion, Impact, Communication

combobox 2
-----------------
selected value: Impact
remaining items in it: Empathy, Persuasion, Communication

combobox 3
-----------------
selected value: Persuasion
remaining items in it: Empathy, Impact, Communication

combobox 4
-----------------
selected value: Communication
remaining items in it: Empathy, Impact, Persuasion


How can i achieve this?
 
M

Mike S

Hi,

I have 4 comboboxes on a form.

In Form_Initialize, i am adding 4 values to each of them. These values
are same for all the 4 combos.
Dim strEPIC() As String
ReDim strEPIC(4)
strEPIC(0) = "Empathy"
strEPIC(1) = "Persuasion"
strEPIC(2) = "Impact"
strEPIC(3) = "Communication"
For i = 0 To 3
Me.ComboBox1.AddItem strEPIC(i)
Me.ComboBox2.AddItem strEPIC(i)
Me.ComboBox3.AddItem strEPIC(i)
Next i
Click to expand...
So my question is:
--------------------------
If i select a value in the 1st combo, the remaining 3 should not show
the value and only allow user to select the remaining 3 values in the
other combos. similarly, if i select a value in 2nd combo, the
remaining 2 combos should not show the selected items in the previous
2 combos i.e. Exclude those values... So each combobox should
eventually have only 1 unique value selected in it and should show
remaining 3 items in its list. e.g.

combobox 1
-----------------
selected value: Empathy
remaining items in it: Persuasion, Impact, Communication

combobox 2
-----------------
selected value: Impact
remaining items in it: Empathy, Persuasion, Communication

combobox 3
-----------------
selected value: Persuasion
remaining items in it: Empathy, Impact, Communication

combobox 4
-----------------
selected value: Communication
remaining items in it: Empathy, Impact, Persuasion


How can i achieve this?
Click to expand...

You could either add or subtract choices in the comboboxes depending on
the user selection in the previous listbox. As long as the user starts
at ComboBox1 and proceeds to Combobox2, then Combobox3, then Combobox4
and never goes back to change a choice, a very simple approach will work:

Private Sub UserForm_Initialize()
ComboBox1.AddItem "A"
ComboBox1.AddItem "B"
ComboBox1.AddItem "C"
ComboBox1.AddItem "D"
ComboBox2.AddItem "A"
ComboBox2.AddItem "B"
ComboBox2.AddItem "C"
ComboBox2.AddItem "D"
ComboBox3.AddItem "A"
ComboBox3.AddItem "B"
ComboBox3.AddItem "C"
ComboBox3.AddItem "D"
ComboBox4.AddItem "A"
ComboBox4.AddItem "B"
ComboBox4.AddItem "C"
ComboBox4.AddItem "D"
End Sub

Private Sub ComboBox1_Change()
Dim n As Long
For n = 0 To ComboBox1.ListCount - 1
If ComboBox1.ListIndex = n Then
ComboBox2.RemoveItem n
ComboBox3.RemoveItem n
ComboBox4.RemoveItem n
Exit For
End If
Next
End Sub

Private Sub ComboBox2_Change()
Dim n As Long
For n = 0 To ComboBox2.ListCount - 1
If ComboBox2.ListIndex = n Then
ComboBox3.RemoveItem n
ComboBox4.RemoveItem n
Exit For
End If
Next
End Sub

Private Sub ComboBox3_Change()
Dim n As Long
For n = 0 To ComboBox3.ListCount - 1
If ComboBox3.ListIndex = n Then
ComboBox4.RemoveItem n
Exit For
End If
Next
End Sub

But if you want to make it more robust you could store the choices in an
array and dynamically load each combobox depending on what the previous
combobox selections are by calling a subroutine each time a combobox
change event is fired.

Will the user be able to change selections, or make selections in any
order?
 
N

noname

You could either add or subtract choices in the comboboxes depending on
the user selection in the previous listbox. As long as the user starts
at ComboBox1 and proceeds to Combobox2, then Combobox3, then Combobox4
and never goes back to change a choice, a very simple approach will work:

Private Sub UserForm_Initialize()
     ComboBox1.AddItem "A"
     ComboBox1.AddItem "B"
     ComboBox1.AddItem "C"
     ComboBox1.AddItem "D"
     ComboBox2.AddItem "A"
     ComboBox2.AddItem "B"
     ComboBox2.AddItem "C"
     ComboBox2.AddItem "D"
     ComboBox3.AddItem "A"
     ComboBox3.AddItem "B"
     ComboBox3.AddItem "C"
     ComboBox3.AddItem "D"
     ComboBox4.AddItem "A"
     ComboBox4.AddItem "B"
     ComboBox4.AddItem "C"
     ComboBox4.AddItem "D"
End Sub

Private Sub ComboBox1_Change()
     Dim n As Long
     For n = 0 To ComboBox1.ListCount - 1
         If ComboBox1.ListIndex = n Then
             ComboBox2.RemoveItem n
             ComboBox3.RemoveItem n
             ComboBox4.RemoveItem n
             Exit For
         End If
     Next
End Sub

Private Sub ComboBox2_Change()
     Dim n As Long
     For n = 0 To ComboBox2.ListCount - 1
         If ComboBox2.ListIndex = n Then
             ComboBox3.RemoveItem n
             ComboBox4.RemoveItem n
             Exit For
         End If
     Next
End Sub

Private Sub ComboBox3_Change()
     Dim n As Long
     For n = 0 To ComboBox3.ListCount - 1
         If ComboBox3.ListIndex = n Then
             ComboBox4.RemoveItem n
             Exit For
         End If
     Next
End Sub

But if you want to make it more robust you could store the choices in an
array and dynamically load each combobox depending on what the previous
combobox selections are by calling a subroutine each time a combobox
change event is fired.

Will the user be able to change selections, or make selections in any
order?
Click to expand...

Yes. User would be able to change selections from any of the combos.
So at any given time, the combos should be dynamic enough to update
themselves depending upon each others combobox values.

right now i tried your code, it works if you start in a sequence from
top to bottom combos, but fails if you start in between or from bottom
to top.

Could you please illustrate your array example so that i can implement
it in my code?

Thanks and best regards. :)
 
M

Mike S

Yes. User would be able to change selections from any of the combos.
So at any given time, the combos should be dynamic enough to update
themselves depending upon each others combobox values.

right now i tried your code, it works if you start in a sequence from
top to bottom combos, but fails if you start in between or from bottom
to top.

Could you please illustrate your array example so that i can implement
it in my code?

Thanks and best regards. :)
Click to expand...

I'm not sure I understand the logic. If all four choices have been made,
and each combobox has a value, then one of them is changed, what should
the other comboboxes do? e.g.

initial condition:

combobox value
1 a
2 c
3 b
4 d

now the user changes 3 to c

- should 1, 2 and 4 all be initialized to have the remaining 3 choices?
- should only the combobox with the matching value be initialized to
have the remaining 3 choices: 2 now includes a, b, c.
- should comboboxes that need a selection indicate that in their text?

What is the behavior you're looking for?
 
N

noname

I'm not sure I understand the logic. If all four choices have been made,
and each combobox has a value, then one of them is changed, what should
the other comboboxes do? e.g.

initial condition:

combobox value
1 a
2 c
3 b
4 d

now the user changes 3 to c

- should 1, 2 and 4 all be initialized to have the remaining 3 choices?
- should only the combobox with the matching value be initialized to
have the remaining 3 choices: 2 now includes a, b, c.
- should comboboxes that need a selection indicate that in their text?

What is the behavior you're looking for?
Click to expand...

Yes, the other 3 should update their list items, but i am not sure if
it would be right to clear their initial existing values on each combo
change event.
--------------------
'i have written the code for only 2 comboboxes, but should be
implemented in all 4 comboboxes change event.

Private Sub ComboBox1_Change()
With ComboBox2
Select Case ComboBox1.Value
Case "Empathy"
If ComboBox1.Value = ComboBox2.Value Or ComboBox1.Value =
ComboBox3.Value Or ComboBox1.Value = ComboBox4.Value Then
.Clear
.AddItem "Persuasion"
.AddItem "Impact"
.AddItem "Communication"
Case "Persuasion"
.Clear
.AddItem "Empathy"
.AddItem "Impact"
.AddItem "Communication"
Case "Impact"
.Clear
.AddItem "Empathy"
.AddItem "Persuasion"
.AddItem "Communication"
Case "Communication"
.Clear
.AddItem "Empathy"
.AddItem "Persuasion"
.AddItem "Impact"
End Select
End With

With ComboBox3
Select Case ComboBox1.Value
Case "Empathy"
.Clear
.AddItem "Persuasion"
.AddItem "Impact"
.AddItem "Communication"
Case "Persuasion"
.Clear
.AddItem "Empathy"
.AddItem "Impact"
.AddItem "Communication"
Case "Impact"
.Clear
.AddItem "Empathy"
.AddItem "Persuasion"
.AddItem "Communication"
Case "Communication"
.Clear
.AddItem "Empathy"
.AddItem "Persuasion"
.AddItem "Impact"
End Select
End With

With ComboBox4
Select Case ComboBox1.Value
Case "Empathy"
.Clear
.AddItem "Persuasion"
.AddItem "Impact"
.AddItem "Communication"
Case "Persuasion"
.Clear
.AddItem "Empathy"
.AddItem "Impact"
.AddItem "Communication"
Case "Impact"
.Clear
.AddItem "Empathy"
.AddItem "Persuasion"
.AddItem "Communication"
Case "Communication"
.Clear
.AddItem "Empathy"
.AddItem "Persuasion"
.AddItem "Impact"
End Select
End With

End Sub

Private Sub ComboBox2_Change()

With ComboBox1
Select Case ComboBox2.Value
Case "Empathy"
.Clear
.AddItem "Persuasion"
.AddItem "Impact"
.AddItem "Communication"
Case "Persuasion"
.Clear
.AddItem "Empathy"
.AddItem "Impact"
.AddItem "Communication"
Case "Impact"
.Clear
.AddItem "Empathy"
.AddItem "Persuasion"
.AddItem "Communication"
Case "Communication"
.Clear
.AddItem "Empathy"
.AddItem "Persuasion"
.AddItem "Impact"
End Select
End With

With ComboBox3
Select Case ComboBox2.Value
Case "Empathy"
.Clear
.AddItem "Persuasion"
.AddItem "Impact"
.AddItem "Communication"
Case "Persuasion"
.Clear
.AddItem "Empathy"
.AddItem "Impact"
.AddItem "Communication"
Case "Impact"
.Clear
.AddItem "Empathy"
.AddItem "Persuasion"
.AddItem "Communication"
Case "Communication"
.Clear
.AddItem "Empathy"
.AddItem "Persuasion"
.AddItem "Impact"
End Select
End With

With ComboBox4
Select Case ComboBox2.Value
Case "Empathy"
.Clear
.AddItem "Persuasion"
.AddItem "Impact"
.AddItem "Communication"
Case "Persuasion"
.Clear
.AddItem "Empathy"
.AddItem "Impact"
.AddItem "Communication"
Case "Impact"
.Clear
.AddItem "Empathy"
.AddItem "Persuasion"
.AddItem "Communication"
Case "Communication"
.Clear
.AddItem "Empathy"
.AddItem "Persuasion"
.AddItem "Impact"
End Select
End With


End Sub

Private Sub UserForm_Initialize()

Dim strEPIC() As String
ReDim strEPIC(4)

strEPIC(0) = "Empathy"
strEPIC(1) = "Persuasion"
strEPIC(2) = "Impact"
strEPIC(3) = "Communication"

For i = 0 To 3
Me.ComboBox1.AddItem strEPIC(i)

Me.ComboBox2.AddItem strEPIC(i)

Me.ComboBox3.AddItem strEPIC(i)

Next i

End Sub
 
M

Mike S

<snip>

Does it behave the way you want? It's complicated and you have to sit
down and figure out exactly how they would behave in every possible
situation... It would be a lot simpler if you required your users to
make selections in the order that you determine. And you could provide
them with the ability to change their choices by providing a 'clear
selections' button and then having them start at the beginning, making
it easy for you to initialize the comboboxes to the known starting
condition. Is that an option?
 
R

rumkus

Noname, why don't you use a hidden sheet as rowsource?
Then on combo's change event you can fire a macro that would update
the list. Like

- Put down all options on column A
- Loop and delete rows checking the used ones

And your comboboxes' rowsource is always refreshed.
If you want to change for instance combo2's "empathy" to "impact"
which is on combobox4,
clear combo4 and pick reappearing "impact" on the list produced by
your refreshing macro.
Obviously you can start where ever you like - just like theatre
booking.
Rgds
 
R

rumkus

Below might give you some idea - tested on cells.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a3:c3:e3:g3")) Is Nothing Then
Call RefreshList
End If
End Sub


Sub RefreshList()
On Error Resume Next

Application.ScreenUpdating = False
Application.EnableEvents = False

Worksheets("HiddenSheet").Range("A1") = "Empathy"
Worksheets("HiddenSheet").Range("A2") = "Persuation"
Worksheets("HiddenSheet").Range("A3") = "Impact"
Worksheets("HiddenSheet").Range("A4") = "Communication"

With Worksheets("HiddenSheet").Columns(1)

.Replace _
What:=Worksheets("select").Range("A3").Value, _
Replacement:="#N/A", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

.Replace _
What:=Worksheets("select").Range("C3").Value, _
Replacement:="#N/A", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

.Replace _
What:=Worksheets("select").Range("E3").Value, _
Replacement:="#N/A", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

.Replace _
What:=Worksheets("select").Range("G3").Value, _
Replacement:="#N/A", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

.SpecialCells(xlConstants, xlErrors).EntireRow.Delete

End With

ActiveWorkbook.Names.Delete Name:="list"
ActiveWorkbook.Names.Add Name:="list",
RefersToR1C1:="=OFFSET(HiddenSheet!R1C1,0,0,COUNTA(HiddenSheet!C1),1)"

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
 
N

noname

<snip>

Does it behave the way you want? It's complicated and you have to sit
down and figure out exactly how they would behave in every possible
situation... It would be a lot simpler if you required your users to
make selections in the order that you determine. And you could provide
them with the ability to change their choices by providing a 'clear
selections' button and then having them start at the beginning, making
it easy for you to initialize the comboboxes to the known starting
condition. Is that an option?
Click to expand...

I think that would be a good solution...right now, i had written some
code which will run on each combo change event...it will loop thru all
the combo controls, excluding itself and check if the current
control's value has already been chosen in the other controls...if so,
a error msgbox is displayed and current combo's Listindex is set to -1
& code does Exit Sub. Else, it will allow the user to keep chosen
value in current combo.
 
N

noname

Below might give you some idea - tested on cells.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a3:c3:e3:g3")) Is Nothing Then
Call RefreshList
End If
End Sub

Sub RefreshList()
  On Error Resume Next

  Application.ScreenUpdating = False
  Application.EnableEvents = False

  Worksheets("HiddenSheet").Range("A1") = "Empathy"
  Worksheets("HiddenSheet").Range("A2") = "Persuation"
  Worksheets("HiddenSheet").Range("A3") = "Impact"
  Worksheets("HiddenSheet").Range("A4") = "Communication"

  With Worksheets("HiddenSheet").Columns(1)

     .Replace _
      What:=Worksheets("select").Range("A3").Value, _
      Replacement:="#N/A", _
      LookAt:=xlPart, _
      SearchOrder:=xlByRows, _
      MatchCase:=False

      .Replace _
      What:=Worksheets("select").Range("C3").Value, _
      Replacement:="#N/A", _
      LookAt:=xlPart, _
      SearchOrder:=xlByRows, _
      MatchCase:=False

      .Replace _
      What:=Worksheets("select").Range("E3").Value, _
      Replacement:="#N/A", _
      LookAt:=xlPart, _
      SearchOrder:=xlByRows, _
      MatchCase:=False

      .Replace _
      What:=Worksheets("select").Range("G3").Value, _
      Replacement:="#N/A", _
      LookAt:=xlPart, _
      SearchOrder:=xlByRows, _
      MatchCase:=False

     .SpecialCells(xlConstants, xlErrors).EntireRow.Delete

  End With

  ActiveWorkbook.Names.Delete Name:="list"
  ActiveWorkbook.Names.Add Name:="list",
RefersToR1C1:="=OFFSET(HiddenSheet!R1C1,0,0,COUNTA(HiddenSheet!C1),1)"

  Application.ScreenUpdating = True
  Application.EnableEvents = True

 End Sub
Click to expand...

Hi Rumkus,

Its a good example, but valid only for a sheet...i am using a Form
having 4 Combos...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

comboboxes 2
populate a combobox based on unique values in another workbook 16
Auto populate value from one task to another in MS PROJECT 0
combo box 2
preventing comboboxes from triggering when sheet copied 2
Comboboxes with multiple values 4
copy unique values into listbox, then modify sheet from these values 4
Combobox list based on choice in previous combobox 0

Top