Opps,,,,

Sorry Tom, please ignore my last post.

I had accidentally chosen a name that had multiple numbers and I
forgot your warning about that situation. I just rechecked and it is
working and working very well (if you discount operator error) ;- }

Thanks again for your help.

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