TextBox RowSource Question

[Minitman]

Greetings,

I have two TextBoxes, TB1 and TB2, on a UserForm, UF1. I need to
enter a number in TB1 and on Exit have it find the name that is
associated with the entered number from a 2 column list in a named
range called List1, this list is sorted alphabetically.

The difficult part is I need to go in either direction. (ie put a
number in TB1 and TB2 shows the corresponding name OR put a name into
TB2 and the corresponding number shows up in TB1). The real problem
is that the names maybe duplicated with different numbers or some
numbers may have more then one name.

Anyone have an idea on where start?

Any help is appreciated.

TIA

-Minitman

[Tom Ogilvy]

That isn't a programming problem - that is a business rule problem. sounds
like you need a combobox for each where, when a value is put in one
combobox, the other combobox fills with the matching possibilities.

You just need to loop through the matching column and do additem for the
matching cell in the other column. You will need to check in the click
event of each if there is already a value in the other combobox so you don't
get circular events.

--
Regards,
Tom Ogilvy



"Minitman" <[email protected]> wrote in message
news:[email protected]...
> Greetings,
>
> I have two TextBoxes, TB1 and TB2, on a UserForm, UF1. I need to
> enter a number in TB1 and on Exit have it find the name that is
> associated with the entered number from a 2 column list in a named
> range called List1, this list is sorted alphabetically.
>
> The difficult part is I need to go in either direction. (ie put a
> number in TB1 and TB2 shows the corresponding name OR put a name into
> TB2 and the corresponding number shows up in TB1). The real problem
> is that the names maybe duplicated with different numbers or some
> numbers may have more then one name.
>
> Anyone have an idea on where start?
>
> Any help is appreciated.
>
> TIA
>
> -Minitman

[Minitman]

Hey Tom,

Thanks for the reply.

What is a business rule?

I see the ComboBoxes for TextBoxes. But not sure how to get the
matching possibilities.

Could you be a bit more specific, please?

-Minitman

On Sun, 13 Feb 2005 19:47:04 -0500, "Tom Ogilvy" <[email protected]>
wrote:

>That isn't a programming problem - that is a business rule problem. sounds
>like you need a combobox for each where, when a value is put in one
>combobox, the other combobox fills with the matching possibilities.
>
>You just need to loop through the matching column and do additem for the
>matching cell in the other column. You will need to check in the click
>event of each if there is already a value in the other combobox so you don't
>get circular events.

[Tom Ogilvy]

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.Value
Combobox2.AddItem cell.Offset(0,1).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(1).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(2).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

Untested, but it should give you some ideas.

--
Regards,
Tom Ogilvy

"Minitman" <[email protected]> wrote in message
news:[email protected]...
> Hey Tom,
>
> Thanks for the reply.
>
> What is a business rule?
>
> I see the ComboBoxes for TextBoxes. But not sure how to get the
> matching possibilities.
>
> Could you be a bit more specific, please?
>
> -Minitman
>
> On Sun, 13 Feb 2005 19:47:04 -0500, "Tom Ogilvy" <[email protected]>
> wrote:
>
> >That isn't a programming problem - that is a business rule problem.
sounds
> >like you need a combobox for each where, when a value is put in one
> >combobox, the other combobox fills with the matching possibilities.
> >
> >You just need to loop through the matching column and do additem for the
> >matching cell in the other column. You will need to check in the click
> >event of each if there is already a value in the other combobox so you
don't
> >get circular events.
>

[Minitman]

Hey Tom,

There are a few items in your code that I am not familiar with. And
that is great! I will now attempt to understand how they works and
make use of them.
Thank you, I would never have even suspected that some of these
commands existed, let alone what they do.

As always, I am in your debt.

-Minitman

On Sun, 13 Feb 2005 20:45:52 -0500, "Tom Ogilvy" <[email protected]>
wrote:

>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.Value
> Combobox2.AddItem cell.Offset(0,1).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(1).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(2).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
>
>Untested, but it should give you some ideas.

[Minitman]

Hey Tom,

I just went looking for bBlockEvent and could find no reference
anywhere. So I have to ask, what is it?

-Minitman



On Sun, 13 Feb 2005 20:45:52 -0500, "Tom Ogilvy" <[email protected]>
wrote:

>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.Value
> Combobox2.AddItem cell.Offset(0,1).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(1).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(2).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
>
>Untested, but it should give you some ideas.

[Minitman]

Hey Tom,

Opps - I misinterpreted what I saw. I get the bBlockEvent!

I am having a bit of an identity problem! ComboBox1 is showing what
should be in ComboBox2 and visa-versa.

I tried playing with it, but nothing reacted as I thought it should!

Any suggestions?

-Minitman


On Sun, 13 Feb 2005 20:45:52 -0500, "Tom Ogilvy" <[email protected]>
wrote:

>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.Value
> Combobox2.AddItem cell.Offset(0,1).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(1).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(2).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
>
>Untested, but it should give you some ideas.

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy


"Minitman" <[email protected]> wrote in message
news:[email protected]...
> Hey Tom,
>
> Opps - I misinterpreted what I saw. I get the bBlockEvent!
>
> I am having a bit of an identity problem! ComboBox1 is showing what
> should be in ComboBox2 and visa-versa.
>
> I tried playing with it, but nothing reacted as I thought it should!
>
> Any suggestions?
>
> -Minitman
>
>
> On Sun, 13 Feb 2005 20:45:52 -0500, "Tom Ogilvy" <[email protected]>
> wrote:
>
> >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.Value
> > Combobox2.AddItem cell.Offset(0,1).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(1).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(2).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
> >
> >Untested, but it should give you some ideas.
>

[Minitman]

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" <[email protected]>
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

[Minitman]

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 <[email protected]>
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" <[email protected]>
>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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>
> 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" <[email protected]>
> >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
>

[Minitman]

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" <[email protected]>
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

[Minitman]

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" <[email protected]>
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.