I have a worksheet that has three columns and two row of data

Orange Score Missed
Red Score Target

I have a form with a listbox and two text boxes

The list box is populated with Orange and Red by using the below code

With Sheets("Sheet1")
Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
For Each c In r
ListBox1.AddItem c
Next c
End With

I want to be able to click on Orange and populate the text box with the
corresponding values.

Can someone point me in the right direction?

Try something like this:

Sub ListBox1_Change()
Dim rng As Range
Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)) _
.Find(ListBox1)
TextBox1 = rng.Offset(, 1)
TextBox2 = rng.Offset(, 2)
End Sub

You may be better off using a multi-column ListBox.

--

Vasant

"dthmtlgod" wrote in message
...
I have a worksheet that has three columns and two row of data

Orange Score Missed
Red Score Target

I have a form with a listbox and two text boxes

The list box is populated with Orange and Red by using the below code

With Sheets("Sheet1")
Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
For Each c In r
ListBox1.AddItem c
Next c
End With

I want to be able to click on Orange and populate the text box with the
corresponding values.

Can someone point me in the right direction?

Thank you sir.

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Try something like this:

Sub ListBox1_Change()
Dim rng As Range
Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)) _
.Find(ListBox1)
TextBox1 = rng.Offset(, 1)
TextBox2 = rng.Offset(, 2)
End Sub

You may be better off using a multi-column ListBox.

--

Vasant

"dthmtlgod" wrote in message
...
I have a worksheet that has three columns and two row of data

Orange Score Missed
Red Score Target

I have a form with a listbox and two text boxes

The list box is populated with Orange and Red by using the below code

With Sheets("Sheet1")
Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
For Each c In r
ListBox1.AddItem c
Next c
End With

I want to be able to click on Orange and populate the text box with the
corresponding values.

Can someone point me in the right direction?

you could as easily used VLOOKUP() on the sheet.
As this is a programming group

TextBox1.Text = _
WorksheetFunction.VLookup(ListBox1.Value, Range("A1:c2"), 2, False)
TextBox2.Text = _
WorksheetFunction.Function.VLookup(ListBox1.Value, Range("A1:c2"), 3,
False)


"Vasant Nanavati" wrote:

Try something like this:

Sub ListBox1_Change()
Dim rng As Range
Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)) _
.Find(ListBox1)
TextBox1 = rng.Offset(, 1)
TextBox2 = rng.Offset(, 2)
End Sub

You may be better off using a multi-column ListBox.

--

Vasant

"dthmtlgod" wrote in message
...
I have a worksheet that has three columns and two row of data

Orange Score Missed
Red Score Target

I have a form with a listbox and two text boxes

The list box is populated with Orange and Red by using the below code

With Sheets("Sheet1")
Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
For Each c In r
ListBox1.AddItem c
Next c
End With

I want to be able to click on Orange and populate the text box with the
corresponding values.

Can someone point me in the right direction?

"Patrick Molloy" wrote in message
...
you could as easily used VLOOKUP() on the sheet.

Now why didn't I think of that? :)

Regards,

Vasant