Hey Tom,

Thanks Tom, I really appreciate your assistance.

It worked for me as well, but in only one direction.

If I choose a number from ComboBox1, the corresponding name does
show up in ComboBox2 - This is good.

However, if I first choose a name from ComboBox2, I don't get anything
in ComboBox1, it stays empty, it should show the corresponding number.

Any suggestions?

TIA

-Minitman
On Tue, 15 Feb 2005 21:16:20 -0500, "Tom Ogilvy"
wrote:

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