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?

On 10/27/2010 1:05 AM, noname wrote:
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?

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?

On Oct 27, 3:28*pm, Mike S wrote:
On 10/27/2010 1:05 AM, noname wrote:









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?

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?

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. :)

On 10/27/2010 4:58 AM, noname wrote:
On Oct 27, 3:28 pm, Mike wrote:
On 10/27/2010 1:05 AM, noname wrote:









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?

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?

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. :)

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?

On Oct 28, 6:25*am, Mike S wrote:
On 10/27/2010 4:58 AM, noname wrote:









On Oct 27, 3:28 pm, Mike *wrote:
On 10/27/2010 1:05 AM, noname wrote:

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?

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?

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. :)

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?

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

On 10/27/2010 11:17 PM, noname wrote:
On Oct 28, 6:25 am, Mike wrote:
On 10/27/2010 4:58 AM, noname wrote:
I have 4 comboboxes on a form.
<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?

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

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

On Oct 28, 1:38*pm, Mike S wrote:
On 10/27/2010 11:17 PM, noname wrote: On Oct 28, 6:25 am, Mike *wrote:
On 10/27/2010 4:58 AM, noname wrote:
I have 4 comboboxes on a form.

<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?

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.

On Oct 29, 12:17*am, " wrote:
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

Hi Rumkus,

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