I don't know your form's control names, so this code uses the defaults given
as I built the form. But it should be easily adapted. This goes 'with' the
Combo box's _Change event. So change the name for the ComboBox and the two
text boxes you want to fill. This acts a bit like a VLOOKUP, but uses .Find
instead to find the item in the Listofprojects list and then does like a
VLOOKUP would, goes over to the right 2 and 5 columns to retrieve data from
columns C and F on the same row that the found item is on in the list.

Private Sub ComboBox1_Change()
Dim listRange As Range
Dim foundItem As Range

Set listRange = Range("Listofprojects") ' spell exactly as defined
Set foundItem = listRange.Find(What:=Me!ComboBox1.Text, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not foundItem Is Nothing Then
'assumes ListOfProjects is in column A
' and data for first text box is in column C
' and data for second text box is in column F
Me!TextBox1.Text = foundItem.Offset(0, 2)
Me!TextBox2.Text = foundItem.Offset(0, 5)
End If
Set listRange = Nothing
End Sub


"BJ&theBear" wrote:

I am learning how to create a userform in Excel 2003 - I have used
Excel for years but have not used VBA since 2001 which was using Excel
97. I am to say the least somewhat rusty. Currently using Excel 2003

My first problem is that I want to have a list or combo box which will
call up a defined list called "Listofprojects". To this end I have
created a listbox with the Rowsource = "Listofprojects" a name
defined list - which displays the various list of projects.

I now want to create a second and third field on the userform which
are automatically populated when a selection is made in listbox1 -
where it will call up the same worksheet "ProjectID" and lookup the
values in columns C and F where the listbox value swelected from the
userform is in column A - but cannot remember where to start. I know
I would normally use the Vlookup function but cannot relate this to
the userform

I am trying to create an idiot proof input form rather than the basic
excel created form which I have tended to use in the past.

Can anyone point me in the right direction

Any help will be greatly appreciated

BJthebear
Scotland
.