Sorry but combo boxs again

[swst]

Trying to understand combo boxs

I have created a user form for a simple datalist
Name, Address, City, Post code

Data entry is through a user form with textboxs - This works fine
Private Sub CommandButton1_Click()
Range("A3").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = TextBox1.Value
ActiveCell.Offset(0, 1) = TextBox2.Value
ActiveCell.Offset(0, 2) = TextBox3.Value
ActiveCell.Offset(0, 3) = TextBox4.Value
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""

End Sub

What i am trying to do is the reverse
Ie Have another userform with a combo box which should be a list of the names on the datasheet
On the choice of a name i want the address details to appear in the textboxs on the new user form


Any suggestions
Steve

 

[P Sitaram]

Use the Find method to the find the value in the names range. Set a variable to the returned range. After that:

TextBox1.Value = rangevar.Offset(0,1) and so on will update the text boxes.

 

[acw]

Hi

The userform has a combobox and 3 textboxes and a command button. Source data is in sheet2 in the range A1:A4 with row 1 as headings.

Code:

this will populate the textboxes when the combobox entry is selected

Private Sub ComboBox1_change()
TextBox1.Text = WorksheetFunction.VLookup(ComboBox1.Value, Range("a2:d4"), 2, False)
TextBox2.Value = WorksheetFunction.VLookup(ComboBox1.Value, Range("a2:d4"), 3, False)
TextBox3.Value = WorksheetFunction.VLookup(ComboBox1.Value, Range("a2:d4"), 4, False)
End Sub

This will close the form
Private Sub CommandButton1_Click()
Unload UserForm1
End Sub

This will fill the combobox
Private Sub UserForm_Initialize()
For Each ce In Sheets("sheet2").Range("a2:a4")
If ce.Value <> "" Then
ComboBox1.AddItem ce.Value
End If
Next ce

End Sub

Tony

 

[swst]

Many thanks Tony

The first 2 parts work perfectly

I dont quite understand the last bit. Are you saying that this will automatically pick up any new names I enter on the 1st user form?
If so I would need to adjust the vlookup ranges each time to also include new data.
or am I talking out of my --s

Steve

 

[acw]

Steve

Yes. I'm working on the principle that you will call your first form to enter the data, close that form, then open the second. If you want the range to include any additional entries, you can either (a) put in a fixed range for the lookup that will cover any expanded entries, (b) dynamically work out the lookup range in the form (al la find the last row etc) or (c) use a named range which is dynaminally updated whenever a new entry is generated.


Tony

 

[HalfAce]

Although I like Sitaram's and Tony's methods, you can also check this post for a method that loops through your data, eliminating the need for a dynamic range.
http://www.mrexcel.com/board2/viewtopic.php?t=130565

If you have a really large data table, I would likely go with one of those other methods.

Dan