Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form / Text Box / Combo Box
Is there anyway when a choice is made from a Combo Box that several other
Text boxes can be filled in Automaticaly. Example: Combo Box Contains 5-6 Different Address's. User picks the Address needed. Text Boxes for City, State & Zip code are automaticaly filled in. I am really trying to learn how to do this stuff, what is the best way to learn how to do all this? It seems for me the best way to learn is to just do it. Thanks Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form / Text Box / Combo Box
The code does the work. The ComboBox has a click event or a change event
that could be used. In either case the code would then issue instructions to find the other related data that corresponds to the selection made in the combobox and based on criteria such as the column or row it is found in, the appropriate text box would be updated with the found data. "Brian" wrote in message ... Is there anyway when a choice is made from a Combo Box that several other Text boxes can be filled in Automaticaly. Example: Combo Box Contains 5-6 Different Address's. User picks the Address needed. Text Boxes for City, State & Zip code are automaticaly filled in. I am really trying to learn how to do this stuff, what is the best way to learn how to do all this? It seems for me the best way to learn is to just do it. Thanks Brian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form / Text Box / Combo Box
Would it be "if this is chosen, then this is the answer type satement"?
How would I do this? Thanks "JLGWhiz" wrote: The code does the work. The ComboBox has a click event or a change event that could be used. In either case the code would then issue instructions to find the other related data that corresponds to the selection made in the combobox and based on criteria such as the column or row it is found in, the appropriate text box would be updated with the found data. "Brian" wrote in message ... Is there anyway when a choice is made from a Combo Box that several other Text boxes can be filled in Automaticaly. Example: Combo Box Contains 5-6 Different Address's. User picks the Address needed. Text Boxes for City, State & Zip code are automaticaly filled in. I am really trying to learn how to do this stuff, what is the best way to learn how to do all this? It seems for me the best way to learn is to just do it. Thanks Brian . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form / Text Box / Combo Box
Assume a UserForm with a ComboBox and three TextBox controls.
The ComboBox is Populated from a RowSource or ListFillRange as appropriate. The data base is on sheet 1 of the active workbook and there is one record per row. Row one is the header row and consists of: Col A: Last Name Col B: First Name, M.I. Col C: Street Address Col D: City Col E: State Col F: Zip Code Col J: Telephone Number TextBox1 = City TextBox2 = State TextBox3 = Zip Code Code in the UserForm code module: Private Sub ComboBox1_Change() Dim sh As Worksheet, lr As Long, rng As Range Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row rng = sh.Range("A2:J" & lr) sAddr = Me.ComboBox1.Value With sh.rng 'Find the selected address Set fAddr = Find(sAddr, LookIn:=xlValues) If Not fAddr Is Nothing Then 'Use the found cell as a reference to get other data. Me.TextBox1.Text = fAddr.Offset(0, 1).Value Me.TextBox2.Text = fAddr.Offset(0, 2),Value Me.TextBox3.Text = fAddr.Offset(0, 3).Value End If End With End Sub This code has not been tested. It is for illustration purposes only. "Brian" wrote in message ... Would it be "if this is chosen, then this is the answer type satement"? How would I do this? Thanks "JLGWhiz" wrote: The code does the work. The ComboBox has a click event or a change event that could be used. In either case the code would then issue instructions to find the other related data that corresponds to the selection made in the combobox and based on criteria such as the column or row it is found in, the appropriate text box would be updated with the found data. "Brian" wrote in message ... Is there anyway when a choice is made from a Combo Box that several other Text boxes can be filled in Automaticaly. Example: Combo Box Contains 5-6 Different Address's. User picks the Address needed. Text Boxes for City, State & Zip code are automaticaly filled in. I am really trying to learn how to do this stuff, what is the best way to learn how to do all this? It seems for me the best way to learn is to just do it. Thanks Brian . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form / Text Box / Combo Box
Corrects syntax error on lines 5 and 8 if code.
Private Sub ComboBox1_Change() Dim sh As Worksheet, lr As Long, rng As Range Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A2:J" & lr) sAddr = Me.ComboBox1.Value With sh.rng 'Find the selected address Set fAddr = .Find(sAddr, LookIn:=xlValues) If Not fAddr Is Nothing Then 'Use the found cell as a reference to get other data. Me.TextBox1.Text = fAddr.Offset(0, 1).Value Me.TextBox2.Text = fAddr.Offset(0, 2),Value Me.TextBox3.Text = fAddr.Offset(0, 3).Value End If End With End Sub This code has not been tested. It is for illustration purposes only. "Brian" wrote in message ... Would it be "if this is chosen, then this is the answer type satement"? How would I do this? Thanks "JLGWhiz" wrote: The code does the work. The ComboBox has a click event or a change event that could be used. In either case the code would then issue instructions to find the other related data that corresponds to the selection made in the combobox and based on criteria such as the column or row it is found in, the appropriate text box would be updated with the found data. "Brian" wrote in message ... Is there anyway when a choice is made from a Combo Box that several other Text boxes can be filled in Automaticaly. Example: Combo Box Contains 5-6 Different Address's. User picks the Address needed. Text Boxes for City, State & Zip code are automaticaly filled in. I am really trying to learn how to do this stuff, what is the best way to learn how to do all this? It seems for me the best way to learn is to just do it. Thanks Brian . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form / Text Box / Combo Box
My User Form is set up as follows with one box below the other:
Combo Box = Address 1 (Street Name) Text Box = Address 2 (Suite #) Text Box = City Text Box = State Text Box = Zip When I pick an address from the Combo Box (Address 1), Address 2, City, State, Zip Autofill with the rest of the information. Where do I put the rest of the Infromation? Is it setup like the "AddItem" would be in the combo Box? I am new to this but trying to learn. Brian "JLGWhiz" wrote: Corrects syntax error on lines 5 and 8 if code. Private Sub ComboBox1_Change() Dim sh As Worksheet, lr As Long, rng As Range Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A2:J" & lr) sAddr = Me.ComboBox1.Value With sh.rng 'Find the selected address Set fAddr = .Find(sAddr, LookIn:=xlValues) If Not fAddr Is Nothing Then 'Use the found cell as a reference to get other data. Me.TextBox1.Text = fAddr.Offset(0, 1).Value Me.TextBox2.Text = fAddr.Offset(0, 2),Value Me.TextBox3.Text = fAddr.Offset(0, 3).Value End If End With End Sub This code has not been tested. It is for illustration purposes only. "Brian" wrote in message ... Would it be "if this is chosen, then this is the answer type satement"? How would I do this? Thanks "JLGWhiz" wrote: The code does the work. The ComboBox has a click event or a change event that could be used. In either case the code would then issue instructions to find the other related data that corresponds to the selection made in the combobox and based on criteria such as the column or row it is found in, the appropriate text box would be updated with the found data. "Brian" wrote in message ... Is there anyway when a choice is made from a Combo Box that several other Text boxes can be filled in Automaticaly. Example: Combo Box Contains 5-6 Different Address's. User picks the Address needed. Text Boxes for City, State & Zip code are automaticaly filled in. I am really trying to learn how to do this stuff, what is the best way to learn how to do all this? It seems for me the best way to learn is to just do it. Thanks Brian . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form / Text Box / Combo Box
How do I know when I need to use a module or class module, it seems that the
books I have read really don't explain much on when to use these items or what they are for. Right now I have Just a User Form and hit the Veiw Code. All my code so far is on this screen, not in any modules, etc. "JLGWhiz" wrote: Corrects syntax error on lines 5 and 8 if code. Private Sub ComboBox1_Change() Dim sh As Worksheet, lr As Long, rng As Range Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A2:J" & lr) sAddr = Me.ComboBox1.Value With sh.rng 'Find the selected address Set fAddr = .Find(sAddr, LookIn:=xlValues) If Not fAddr Is Nothing Then 'Use the found cell as a reference to get other data. Me.TextBox1.Text = fAddr.Offset(0, 1).Value Me.TextBox2.Text = fAddr.Offset(0, 2),Value Me.TextBox3.Text = fAddr.Offset(0, 3).Value End If End With End Sub This code has not been tested. It is for illustration purposes only. "Brian" wrote in message ... Would it be "if this is chosen, then this is the answer type satement"? How would I do this? Thanks "JLGWhiz" wrote: The code does the work. The ComboBox has a click event or a change event that could be used. In either case the code would then issue instructions to find the other related data that corresponds to the selection made in the combobox and based on criteria such as the column or row it is found in, the appropriate text box would be updated with the found data. "Brian" wrote in message ... Is there anyway when a choice is made from a Combo Box that several other Text boxes can be filled in Automaticaly. Example: Combo Box Contains 5-6 Different Address's. User picks the Address needed. Text Boxes for City, State & Zip code are automaticaly filled in. I am really trying to learn how to do this stuff, what is the best way to learn how to do all this? It seems for me the best way to learn is to just do it. Thanks Brian . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form / Text Box / Combo Box
I think you need to work with the information at this site for a while.
http://msdn.microsoft.com/en-us/library/bb211363.aspx "Brian" wrote in message ... How do I know when I need to use a module or class module, it seems that the books I have read really don't explain much on when to use these items or what they are for. Right now I have Just a User Form and hit the Veiw Code. All my code so far is on this screen, not in any modules, etc. "JLGWhiz" wrote: Corrects syntax error on lines 5 and 8 if code. Private Sub ComboBox1_Change() Dim sh As Worksheet, lr As Long, rng As Range Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A2:J" & lr) sAddr = Me.ComboBox1.Value With sh.rng 'Find the selected address Set fAddr = .Find(sAddr, LookIn:=xlValues) If Not fAddr Is Nothing Then 'Use the found cell as a reference to get other data. Me.TextBox1.Text = fAddr.Offset(0, 1).Value Me.TextBox2.Text = fAddr.Offset(0, 2),Value Me.TextBox3.Text = fAddr.Offset(0, 3).Value End If End With End Sub This code has not been tested. It is for illustration purposes only. "Brian" wrote in message ... Would it be "if this is chosen, then this is the answer type satement"? How would I do this? Thanks "JLGWhiz" wrote: The code does the work. The ComboBox has a click event or a change event that could be used. In either case the code would then issue instructions to find the other related data that corresponds to the selection made in the combobox and based on criteria such as the column or row it is found in, the appropriate text box would be updated with the found data. "Brian" wrote in message ... Is there anyway when a choice is made from a Combo Box that several other Text boxes can be filled in Automaticaly. Example: Combo Box Contains 5-6 Different Address's. User picks the Address needed. Text Boxes for City, State & Zip code are automaticaly filled in. I am really trying to learn how to do this stuff, what is the best way to learn how to do all this? It seems for me the best way to learn is to just do it. Thanks Brian . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User form Text Box & Combo Box. | Excel Programming | |||
User form combo box | Excel Programming | |||
Combo box on user form | Excel Programming | |||
combo box in a user form | Excel Programming | |||
Can't seem to get Combo Box on User Form | Excel Programming |