If there is only a single choice in the other combobo for the selected item,
then both comboboxes will have values. If there are multiple choices, then
the other combobox will appear blank and the user can make a restricted
selection from the other combobox.

Public bBlockEvents As Boolean

Private Sub Userform_Initialize()
Dim cell As Range
bBlockEvents = True
ComboBox1.Clear
ComboBox2.Clear
For Each cell In Range("List").Columns(1).Cells
ComboBox1.AddItem cell.Offset(0, 1).Value
ComboBox2.AddItem cell.Value
Next
bBlockEvents = False
End Sub

Private Sub Combobox1_Click()
If bBlockEvents = True Then Exit Sub
If ComboBox1.Value = "" Then Exit Sub
If Trim(ComboBox2.Value) = "" Then
On Error GoTo ErrHandler
bBlockEvents = True
ComboBox2.Clear
For Each cell In Range("List").Columns(2).Cells
If LCase(cell.Value) = LCase(ComboBox1.Value) Then
ComboBox2.AddItem cell.Offset(0, -1).Value
End If
Next
If ComboBox2.ListCount 1 Then
ComboBox2.ListIndex = -1
Else
ComboBox2.ListIndex = 0
End If
End If
ErrHandler:
bBlockEvents = False
End Sub


Private Sub Combobox2_Click()
If bBlockEvents = True Then Exit Sub
If ComboBox2.Value = "" Then Exit Sub
If Trim(ComboBox1.Value) = "" Then
On Error GoTo ErrHandler
bBlockEvents = True
ComboBox1.Clear
For Each cell In Range("List").Columns(1).Cells
If LCase(cell.Value) = LCase(ComboBox2.Value) Then
ComboBox1.AddItem cell.Offset(0, 1).Value
End If
Next
If ComboBox1.ListCount 1 Then
ComboBox1.ListIndex = -1
Else
ComboBox1.ListIndex = 0
End If
End If

ErrHandler:
bBlockEvents = False
End Sub

I made some modifications and I tested it and it worked for me.

--
Regards,
Tom Ogilvy



"Minitman" wrote in message
...
Hey Tom,

I thought I should be a bit more exact in my decryption of what is
happening,

Which ever ComboBox I pick an entry from the other show hot the
corresponding entry, but only the first item in it's list.

Example:

List:
Column 1 Column 2
ABC Group 458-5584
DEG Group 325-4515
m&m Group 235-4589

ComboBox 2 choose m&m Group and ComboBox 1 should show 235-4589
instead it shows 458-5584. Or in ComboBox1 choose 325-4515 and you
should see DEG Group in ComboBox 2, instead you see ABC Group.

Can this be fixed? Any help would be appreciated.

TIA

-Minitman


On Tue, 15 Feb 2005 08:15:55 -0600, Minitman
wrote:

Hey Tom.

I tried this and it somewhat works. I still need help to get it to be
interactive.

Each ComboBox will now show the correct list, but if I choose any of
the items in either ComboBox the other ComboBox is supposed to show
the corresponding entry in it or better yet show this in the
corresponding TextBox. (I have 2 ComboBoxes and 2 TextBoxes)

Is this even possible? Any help is appreciated.

TIA

-Minitman


On Mon, 14 Feb 2005 08:35:10 -0500, "Tom Ogilvy"
wrote:

I believe I have made the changes to reverse what appears in which
combobox.

Public bBlockEvent as Boolean

Private Sub Userform_Initialize()
Dim cell as Range
bBlockEvents = True
combobox1.Clear
combobox2.Clear
for each cell in Range("List").Columns(1).Cells
Combobox1.AddItem cell.Offet(0,1).Value
Combobox2.AddItem cell.Value
Next
bBlockEvents = False
End Sub

Private Sub Combobox1_Click()
if bBlockEvents = True then exit sub
If Combobox1.Value = "" then exit sub
if Trim(Combobox2.Value) = "" then
on Error goto ErrHandler
bBlockEvents = True
for each cell in Range("List").columns(2).Cells
if lcase(cell.Value) = Combobox1.Value then
combobox2.AddItem cell.Offset(0,-1).Value
end if
Next
End if
combobox2.ListIndex = 0
ErrHandler:
bBlockEvents = False
End Sub

Private Sub Combobox2_Click()
if bBlockEvents = True then exit sub
If Combobox2.Value = "" then exit sub
If Trim(Combobox1.Value) = "" then
on Error goto ErrHandler
bBlockEvents = True
for each cell in Range("List").columns(1).Cells
if lcase(cell.Value) = Combobox2.Value then
combobox1.AddItem cell.Offset(0,1).Value
end if
Next
End if
combobox1.ListIndex = 0
ErrHandler:
bBlockEvents = False
End Sub