Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX AND TEXTBOXES QUESTION !!
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX AND TEXTBOXES QUESTION !!
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" wrote in message ... 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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX AND TEXTBOXES QUESTION !!
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX AND TEXTBOXES QUESTION !!
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" wrote in message ... 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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX AND TEXTBOXES QUESTION !!
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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX AND TEXTBOXES QUESTION !!
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" wrote in message ... 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 *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX AND TEXTBOXES QUESTION !!
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combobox selection to fill in textboxes | Excel Programming | |||
Load UserForm ComboBox And Autofill 124 TextBoxes | Excel Programming | |||
Individual values from a Combobox List placed in textboxes | Excel Programming | |||
Filling Textboxes from Combobox selection | Excel Programming | |||
searching for a combobox.value and filling in textboxes from results | Excel Programming |