• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 693
  • Last Modified:

Update Textboxes in Excel based on value selected in a ComboBox

In a spreadsheet of Excel, I placed a combobox that shows a range of cells from another sheet (Sheet_Ex)in the same workbook.  I have two other Textboxes that show a different range of cells also from Sheet_Ex.  How can update my last two textboxes based on what I chose in the combobox?  So if the combobox shows all the street names and I select a street, how can one textbox show the length of that street and the other textbox show the Width?  

I simply used this method to add controls into my excel spreadsheet - http://techonthenet.com/excel/questions/create_combo.php

Can I continue with that method or do I have to actually make a UserForm and update it?

Regards,
0
JoshinHtown
Asked:
JoshinHtown
  • 6
  • 6
1 Solution
 
Jeroen RosinkSoftware testing consultantCommented:
I think the best way is instead of using combobox from the forms menu, use from the Control ToolBox

In the properties of this combobox you can define your ListFillRange where data should come from.

If you use this combobox you might use the ComboBox1_Change event like:

Private Sub ComboBox1_Change()
    TextBox1 = ComboBox1.Value
    TextBox2 = ComboBox1.Value
End Sub
0
 
JoshinHtownAuthor Commented:
Hi roos01-  I decided to put my combobox and 2 textboxes in a UserForm so that I could use the AfterUpdate event of the combobox.  If I do what you are saying and say Textbox1 = Combobox1.Value  then how does it know what value to pick up?  I tried Textbox1 = Combobox1.Column(10) because the 10th column is the length field and I got a "Could not get the column property, Invalid Argument" error.  Any ideas?
0
 
Jeroen RosinkSoftware testing consultantCommented:
Ok, you are using a multiple column combobox.

try this instead.
This code is to fille combobox with data stored on active sheet. I set the columnCount to 10
Private Sub UserForm_initialize()
Dim ws1 As Worksheet
Dim RowsA As Long

RowsA = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To RowsA
With ComboBox1
.AddItem Cells(i, 1)
.List(i - 1, 1) = Cells(i, 2)
.List(i - 1, 2) = Cells(i, 3)
.List(i - 1, 3) = Cells(i, 4)
.List(i - 1, 4) = Cells(i, 5)
.List(i - 1, 5) = Cells(i, 6)
.List(i - 1, 6) = Cells(i, 7)
.List(i - 1, 7) = Cells(i, 8)
.List(i - 1, 8) = Cells(i, 9)
.List(i - 1, 9) = Cells(i, 10)

End With
Next

End Sub


this code is to fill the combobox based on the selected row:
Private Sub ComboBox1_Change()
TextBox1 = ComboBox1.List(ComboBox1.ListIndex, 9)
TextBox2 = ComboBox1.List(ComboBox1.ListIndex, 5)
End Sub

keep in mind that the column is always counting starting from 0. So if you need the value of column 10 then this means you need to use value: 9

Hopeing this helps you out
regards,
Jeroen
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
JoshinHtownAuthor Commented:
Cool Jeroen- In your code how does it know what sheet to use?  I have a couple of different sheets.

Regards,
0
 
Jeroen RosinkSoftware testing consultantCommented:
at this moment it is just the active sheet.
If you have a specific sheet then try this to load the combobox:
Private Sub UserForm_initialize()
Dim ws1 As Worksheet
Dim RowsA As Long
set ws1 = Sheets("YourSheetName")
RowsA = ws1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To RowsA
With ComboBox1
.AddItem ws1.Cells(i, 1)
.List(i - 1, 1) = ws1.Cells(i, 2)
.List(i - 1, 2) = ws1.Cells(i, 3)
.List(i - 1, 3) = ws1.Cells(i, 4)
.List(i - 1, 4) = ws1.Cells(i, 5)
.List(i - 1, 5) = ws1.Cells(i, 6)
.List(i - 1, 6) = ws1.Cells(i, 7)
.List(i - 1, 7) = ws1.Cells(i, 8)
.List(i - 1, 8) = ws1.Cells(i, 9)
.List(i - 1, 9) = ws1.Cells(i, 10)

End With
Next

End Sub
0
 
JoshinHtownAuthor Commented:
Excellent work Jeroen..
0
 
Jeroen RosinkSoftware testing consultantCommented:
Glad I gcould help.
thanks for the grade!
Jeroen
0
 
JoshinHtownAuthor Commented:
Jeroen-

If I wanted to include more than 10 columns why can't I do this?

For i = 1 To RowsA
With ComboBox1
.AddItem ws1.Cells(i, 1)
.List(i - 1, 1) = ws1.Cells(i, 2)
.List(i - 1, 2) = ws1.Cells(i, 3)
.List(i - 1, 3) = ws1.Cells(i, 4)
.List(i - 1, 4) = ws1.Cells(i, 5)
.List(i - 1, 5) = ws1.Cells(i, 6)
.List(i - 1, 6) = ws1.Cells(i, 7)
.List(i - 1, 7) = ws1.Cells(i, 8)
.List(i - 1, 8) = ws1.Cells(i, 9)
.List(i - 1, 9) = ws1.Cells(i, 10)
.List(i - 1, 10) = ws1.Cells(i, 11)
.List(i - 1, 11) = ws1.Cells(i, 12)
.List(i - 1, 12) = ws1.Cells(i, 13)
.List(i - 1, 13) = ws1.Cells(i, 14)


End With
Next

'I get an error after .List(i - 1, 10) = ws1.Cells(i, 11)
0
 
Jeroen RosinkSoftware testing consultantCommented:
If im correct there is a restriction on columns in as well listboxes as comboboxes. if im correct then the maximum is 10.

You might try to raise the column count property. to 14, if this gives the error then that is the cause.


0
 
JoshinHtownAuthor Commented:
That's ashame if I can't go past 10 columns.  Where do I go to try to increase the size past 10?
0
 
Jeroen RosinkSoftware testing consultantCommented:
to increase the size over 10 columns? I think you will get into additional controls which will be hard to attached to workbooks so not all users can use/see it.

Im just wondering, aint >10 columns not to much information?
perhaps you can place a listbox on the userform where the user is able to select up to 10 items they want to see.
0
 
JoshinHtownAuthor Commented:
Hi roos01-  I have a table that has about 22 columns of information that I need to get ahold of in my excel form.  It would be perfect if I could extend your code to include 12 more columns in the list.  So there is just one combobox that pulls all the street names from a spreadsheet and based on what I pick, all the textboxes pull from most of the other columns to show the information about that street (length, width, year built, material, etc, etc..)

Does that make sense?

Regards,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now