I have what I think is going to be an easy (easier anyway) question. I
have an excel spreadsheet with about 1000 rows of data with 3 columns.
I have a userform setup with a combo box. What I would like to see, is
when a value from the combo box is entered (from column A) I would like
that value of a text box to show the item found in Column B of the same
row. So if the value is from A600, then the value in the textbox is
from B600.

you can give this a try. it's a userform with 1 combobox and 2 textboxes.
when you enter or choose a value from the combobox, the values from that row in
columns B and C are entered into the 2 textboxes
if you don't want to fire on change, but when the combobox is exited, then use:
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
as the first line instead.

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim lastrow As Long, frow As Long, rng As Range, rngfound As Range
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
frow = ws.Range("A1").Row
Set rng = ws.Range("A" & frow & ":A" & lastrow)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With rng
On Error Resume Next
Set rngfound = .Find(What:=Me.ComboBox1.Value, LookIn:=xlValues, _
LookAt:=xlWhole)
If Not rngfound Is Nothing Then
With Me
.TextBox1.Value = Range("B" & rngfound.Row)
.TextBox2.Value = Range("C" & rngfound.Row)
End With
Else
MsgBox "Item number not found."
Me.ComboBox1.Value = ""
Exit Sub
End If
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

--


Gary


wrote in message
ups.com...
I have what I think is going to be an easy (easier anyway) question. I
have an excel spreadsheet with about 1000 rows of data with 3 columns.
I have a userform setup with a combo box. What I would like to see, is
when a value from the combo box is entered (from column A) I would like
that value of a text box to show the item found in Column B of the same
row. So if the value is from A600, then the value in the textbox is
from B600.

Hi Jeff,

Sorry, I found a bug in my code (the part where it loops through the rows to
populate the combobox). It should be this instead:

Dim rng As range
Dim lngCount As Long
For Each rng In range("A1", range("A65536").End(xlUp))
ComboBox1.AddItem rng
ComboBox1.List(lngCount, 1) = rng.Offset(lngCount, 1)
lngCount = lngCount + 1
Next




"dmthornton" wrote:

Hi Jeff,

The suggestions made should work, but I would probably do it differently by
setting my combobox to use 2 columns and then have it set the textbox value
to the second column whenever there is a change.

Example using a form with just a combobox and textbox:


Private Sub UserForm_Activate()
'Change the column count to include 2 columns
ComboBox1.ColumnCount = 2

'Set column widths
'
'You should set the first column width large enough so that
'it doesn't cut-off the value when displayed.
'Set the width of the second column to 0 (so that it's hidden)
ComboBox1.ColumnWidths = "100,0"

'Add list items
'
'I don't know how you are populating the combobox, but
'here's a basic example (note: There are many ways to do
'this, but the key is to use List(index,column) to populate
'the second column values)
Dim rng As range
For Each rng In range("A1", range("A65536").End(xlUp))
ComboBox1.AddItem rng
ComboBox1.List(0, 1) = rng.Offset(0, 1)
Next
End Sub

Private Sub ComboBox1_Change()
'Set the textbox value to the combobox's second column value
On Error Resume Next
TextBox1.Value = ComboBox1.List(ComboBox1.ListIndex, 1)
On Error GoTo 0
End Sub


Like I mentioned, the other suggestions should work, this is just another
way of doing it.


Dave



" wrote:

I have what I think is going to be an easy (easier anyway) question. I
have an excel spreadsheet with about 1000 rows of data with 3 columns.
I have a userform setup with a combo box. What I would like to see, is
when a value from the combo box is entered (from column A) I would like
that value of a text box to show the item found in Column B of the same
row. So if the value is from A600, then the value in the textbox is
from B600.

Thanks...for all the responses. I'm liking the use of the command
button, from John. It seems to work almost like I want. In my
original note I excluded some info only to simplify my question.

What I'm really needing is searching column A with what is added to a
listbox, four items. It's almost working exactly as I wish. However
there is the matter that when there is no match, the return in the
textbox is the first value found....

Here is a better (I hope) example of some test data I'm using:

Column A Column B Column C Column D Column E
ATL JFK 10.00 0.15 ATL
ATL MIA 15.00 0.23 BOS
ATL ORD 20.00 0.49 XXX
BOS JFK 30.00 1.87
BOS MIA 25.00 0.99
BOS ORD 45.00 0.46
XXX LAX 55.00 0.12

Column E will be unique values, filtered out from Column A. Column A,
B, C, D will have mixed values.

I have a ComboBox with the rowsource is E2:E4.

Then the code you provided, in part:
ComboBox additem: "JFK", "ORD", "MIA", "LAX"
J = "JFK"
If Cells(myRow, 1) = ComboBox1.Value And Cells(myRow, 2) = J Then
TextBox1.Value = Cells(myRow, 3)

Again, this all works well. Unitl I come to a point were there is no
match, ie. Column A's XXX, what I get for say JFK is 10.00 when in fact
there is no column B match. I'd like to have verbage added to the
textbox that says "No Rate" when there is no match from column A and B,
or the ListBox1 and the Combobox.

Also, I have the 'additem' for the combobox for: JFK, MIA, LAX, ORD.
Column B holds many more but I only want to see the four codes. That
part is working fine. It's just when there is no match. I've tired
the If...Then...Else textbox1 = "No Rate", which when I have that in
the code, all I get is 'No Rate' in the textbox, even when there is a
match...

When I say a match I mean in a row, for example, when ATL is in Column
A20 and JFK is is in B20, then show me C20 and D20 in textbox 1 and 2.
If ATL is in Column A23 and MIA is in B23, then show me C23 and D23 in
textbox 3 and 4....and so on...make sense?

So, in my example above when XXX is selected in the ListBox, then
Textbox should show NO rate for every item but LAX where it would show
55.00 and 0.12

Thanks again John you really pretty much nailed it on with your
example.






John Bundy wrote:
Try this out:
Private Sub CommandButton1_Click()
Dim myRow As Integer

lastrow = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
myRow = 1
Do Until myRow = lastrow
If Cells(myRow, 1) = ComboBox1.Value Then TextBox1.Text = Cells(myRow, 2)
myRow = myRow + 1
Loop

End Sub
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


" wrote:

I have what I think is going to be an easy (easier anyway) question. I
have an excel spreadsheet with about 1000 rows of data with 3 columns.
I have a userform setup with a combo box. What I would like to see, is
when a value from the combo box is entered (from column A) I would like
that value of a text box to show the item found in Column B of the same
row. So if the value is from A600, then the value in the textbox is
from B600.

Just a quick note, thanks to all that replied. I was able to get what
I wanted from John's original suggestion. I did run into a problem as
noted in my previous message but I was able to resolve that as
well...thanks again!!


wrote:
Thanks...for all the responses. I'm liking the use of the command
button, from John. It seems to work almost like I want. In my
original note I excluded some info only to simplify my question.

What I'm really needing is searching column A with what is added to a
listbox, four items. It's almost working exactly as I wish. However
there is the matter that when there is no match, the return in the
textbox is the first value found....

Here is a better (I hope) example of some test data I'm using:

Column A Column B Column C Column D Column E
ATL JFK 10.00 0.15 ATL
ATL MIA 15.00 0.23 BOS
ATL ORD 20.00 0.49 XXX
BOS JFK 30.00 1.87
BOS MIA 25.00 0.99
BOS ORD 45.00 0.46
XXX LAX 55.00 0.12

Column E will be unique values, filtered out from Column A. Column A,
B, C, D will have mixed values.

I have a ComboBox with the rowsource is E2:E4.

Then the code you provided, in part:
ComboBox additem: "JFK", "ORD", "MIA", "LAX"
J = "JFK"
If Cells(myRow, 1) = ComboBox1.Value And Cells(myRow, 2) = J Then
TextBox1.Value = Cells(myRow, 3)

Again, this all works well. Unitl I come to a point were there is no
match, ie. Column A's XXX, what I get for say JFK is 10.00 when in fact
there is no column B match. I'd like to have verbage added to the
textbox that says "No Rate" when there is no match from column A and B,
or the ListBox1 and the Combobox.

Also, I have the 'additem' for the combobox for: JFK, MIA, LAX, ORD.
Column B holds many more but I only want to see the four codes. That
part is working fine. It's just when there is no match. I've tired
the If...Then...Else textbox1 = "No Rate", which when I have that in
the code, all I get is 'No Rate' in the textbox, even when there is a
match...

When I say a match I mean in a row, for example, when ATL is in Column
A20 and JFK is is in B20, then show me C20 and D20 in textbox 1 and 2.
If ATL is in Column A23 and MIA is in B23, then show me C23 and D23 in
textbox 3 and 4....and so on...make sense?

So, in my example above when XXX is selected in the ListBox, then
Textbox should show NO rate for every item but LAX where it would show
55.00 and 0.12

Thanks again John you really pretty much nailed it on with your
example.






John Bundy wrote:
Try this out:
Private Sub CommandButton1_Click()
Dim myRow As Integer

lastrow = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
myRow = 1
Do Until myRow = lastrow
If Cells(myRow, 1) = ComboBox1.Value Then TextBox1.Text = Cells(myRow, 2)
myRow = myRow + 1
Loop

End Sub
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


" wrote:

I have what I think is going to be an easy (easier anyway) question. I
have an excel spreadsheet with about 1000 rows of data with 3 columns.
I have a userform setup with a combo box. What I would like to see, is
when a value from the combo box is entered (from column A) I would like
that value of a text box to show the item found in Column B of the same
row. So if the value is from A600, then the value in the textbox is
from B600.