?
Solved

Update Textboxes in Excel based on value selected in a ComboBox

Posted on 2007-10-18
12
Medium Priority
?
687 Views
Last Modified: 2008-03-06
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
Comment
Question by:JoshinHtown
  • 6
  • 6
12 Comments
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20103691
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
 

Author Comment

by:JoshinHtown
ID: 20103788
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
 
LVL 33

Accepted Solution

by:
Jeroen Rosink earned 500 total points
ID: 20103919
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

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

Regards,
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20104135
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
 

Author Comment

by:JoshinHtown
ID: 20104156
Excellent work Jeroen..
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20104164
Glad I gcould help.
thanks for the grade!
Jeroen
0
 

Author Comment

by:JoshinHtown
ID: 20104344
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
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20104366
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
 

Author Comment

by:JoshinHtown
ID: 20104732
That's ashame if I can't go past 10 columns.  Where do I go to try to increase the size past 10?
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20106688
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
 

Author Comment

by:JoshinHtown
ID: 20108990
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question