Hello -

On a UserForm, I have Combobox1, Textbox1 and Textbox2.
Values in Combobox1 come from Range("A:A"). What I want is to be able to
select a value in Combobox1 and its corresponding values from
Range("B:B") and Range("C:C") will automatically populate in Textbox1
and Textbox2 respectively.

Example 1: If I select a dropdown value in Combobox1 that happens to be
from Range("A6"), then Textbox1 should automatically populate value in
Range("B6") and Textbox2 will also populate value in Range("C6") on the
form.

Example 2: If I select a dropdown value in Combobox1 that happens to be
from Range("A10"), then Textbox1 should automatically populate value in
Range("B10") and Textbox2 will also populate value in Range("C10")on the
form.

Any help would be appreciated.
Thanks
Jay

*** Sent via Developersdex http://www.developersdex.com ***

This should do what you want...

Private Sub ComboBox1_Change()
TextBox1.Value = Cells(ComboBox1.ListIndex + 1, "B")
TextBox2.Value = Cells(ComboBox1.ListIndex + 1, "C")
End Sub

--
Rick (MVP - Excel)


"jay dean" <(E-Mail Removed)> wrote in message
news:evc6l$(E-Mail Removed)...
> Hello -
>
> On a UserForm, I have Combobox1, Textbox1 and Textbox2.
> Values in Combobox1 come from Range("A:A"). What I want is to be able to
> select a value in Combobox1 and its corresponding values from
> Range("B:B") and Range("C:C") will automatically populate in Textbox1
> and Textbox2 respectively.
>
> Example 1: If I select a dropdown value in Combobox1 that happens to be
> from Range("A6"), then Textbox1 should automatically populate value in
> Range("B6") and Textbox2 will also populate value in Range("C6") on the
> form.
>
> Example 2: If I select a dropdown value in Combobox1 that happens to be
> from Range("A10"), then Textbox1 should automatically populate value in
> Range("B10") and Textbox2 will also populate value in Range("C10")on the
> form.
>
> Any help would be appreciated.
> Thanks
> Jay
>
> *** Sent via Developersdex http://www.developersdex.com ***

Rick,

Thanks, but perhaps I should have clarified this. The Combobox1 values
are loaded from Worksheets("TEST").Range("A:A") from the
UserForm_Initialize() sub.

So, the values that go into Textbox1 and Textbox2 should also come from
Worksheets("TEST").Range("B:B")
and Worksheets("TEST").Range("C:C").

When I assigned your code to the Combobox1 Change sub, I got "Runtime
error 1004. Application-defined or object-defined error." Do you think
it has to do with the way you are referencing the values into the
textboxes, maybe?

Thanks
Jay Dean


*** Sent via Developersdex http://www.developersdex.com ***

I'm not sure why you are getting the error, but when I set the everything up
the way I think you have it set up, this event code works for me (the
assumption being that the ComboBox is really filled starting with cell A1 on
the TEST sheet... needed so the ListIndex plus one coordinates with the rows
for the values in the ComboBox)...

Private Sub ComboBox1_Change()
TextBox1.Value = Worksheets("TEST").Cells(ComboBox1.ListIndex + 1, "B")
TextBox2.Value = Worksheets("TEST").Cells(ComboBox1.ListIndex + 1, "C")
End Sub

--
Rick (MVP - Excel)


"jay dean" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Rick,
>
> Thanks, but perhaps I should have clarified this. The Combobox1 values
> are loaded from Worksheets("TEST").Range("A:A") from the
> UserForm_Initialize() sub.
>
> So, the values that go into Textbox1 and Textbox2 should also come from
> Worksheets("TEST").Range("B:B")
> and Worksheets("TEST").Range("C:C").
>
> When I assigned your code to the Combobox1 Change sub, I got "Runtime
> error 1004. Application-defined or object-defined error." Do you think
> it has to do with the way you are referencing the values into the
> textboxes, maybe?
>
> Thanks
> Jay Dean
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

I actually loaded the data from Range("A2:A250"), that is, I did not
start from A1. This should not be an issue right?



*** Sent via Developersdex http://www.developersdex.com ***

This is a perfect example of why people posting questions on newsgroups
should *not* simplify their questions for us... our answers (code and/or
formulas) are highly dependent on the layout they will be applied against...
when you simplify your question, you end up getting an answer to a question
that doesn't really apply to what you need. Fortunately, the modification
needed for your actual layout is easy enough to implement; we just have to
adjust the relationship between the ListIndex value and the row number
offset to the start of your data. Try this...

Private Sub ComboBox1_Change()
TextBox1.Value = Worksheets("TEST").Cells(ComboBox1.ListIndex + 2, "B")
TextBox2.Value = Worksheets("TEST").Cells(ComboBox1.ListIndex + 2, "C")
End Sub

--
Rick (MVP - Excel)


"jay dean" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I actually loaded the data from Range("A2:A250"), that is, I did not
> start from A1. This should not be an issue right?
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

Thanks, Rick.
It worked perfectly and I am sorry I simplified the question when I
wasn't supposed to. I really appreciate this!!!

Jay Dean


*** Sent via Developersdex http://www.developersdex.com ***