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

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" wrote in message
...
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

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

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" wrote in message
...
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"
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.

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

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

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

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" wrote in message
...
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"
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.