COMBOBOX AND TEXTBOXES QUESTION !!

[jay dean]

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

 

[Rick Rothstein]

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

 

[jay dean]

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

 

[Rick Rothstein]

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

 

[jay dean]

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

 

[Rick Rothstein]

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

 

[jay dean]

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