using a combo box to populate a form

[ashleywanless]

Hi,

I have coded a combo box to populate with relevant data. What i want to do is when someone selects something from the combo box and clicks ok at the bottom of the form that another form is opened and all fields populated acording to the entry selected in the combo box, is this possible?

Does anyone have any links to examples?

Thanks

Ashley

 

[bobsan42]

yes it is possible - do open the new form without closing the other one:
UserForm2.Show
in the new form during Initialize refer to the values you need from the other one:
Me.Textbox1.value=UserForm1.Combobox1.Value
in general that's it more or less

 

[ashleywanless]

Ok just to explain further...

All my data is in excel, 46 columns of it! I know how to create and code either a combo box or list box to show only the data i need (uniquie ID, Desc). Then i want the user to highlight the relevant entry in the list and when they then click the command button it opens a new form and loads the form with the corresponding data in the excel sheet. I guess this is more complicated than just coding the userform 2 to use vlookups to pull in the right data? any knowledge of examples for this?

My data source has unnique id in column a, description in b and then columns and columns of data after, i want some of these columns to be the source data for the second form, that make sense?

Thanks

 

[bobsan42]

OK - it is usually better to put ALL the data in the combobox and ste the width of the columns you don't want to see to 0, then take what you need in the next form directly from the selected record in the combobox.
well maybe it is not very practical to do it with 46 columns. since A has the unique ID put column A & B in the combobox (more if you need), but let the bound column be 1 (which means column A).
open UserForm2 (would be good if possible to use a loop - use consecutive numbered names for the controls): for example name all your controls field1, field2, .....

Private Sub UserForm_Initialize()
Dim uID as Range
Set uID = activesheet.Range("A:A").find(what:=UserForm1.Combobox1.Value, lookin:=xlvalues)
for i = 0 to 45
me.Contol("field" & i).value = uID.Offset(0,i).value
Next i
End Sub

HiH