User Form / Text Box / Combo Box

[Brian]

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

 

[JLGWhiz]

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]

Would it be "if this is chosen, then this is the answer type satement"?

How would I do this?

Thanks

 

[JLGWhiz]

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.

 

[JLGWhiz]

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]

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

 

[Brian]

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]

I think you need to work with the information at this site for a while.

http://msdn.microsoft.com/en-us/library/bb211363.aspx