Creating a lookup in a userform

[BJ&theBear]

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

 

[JLatham]

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]

Thanks for the help - I still cannot get the textboxes to show
anything - is there any control source or row source entry required in
textbox1 or textbox2?

I really am terribly rusty

Thanks again

BJ

 

[JLatham]

If the other controls are just plain text boxes, they don't need a row source
entry. Text boxes just get data typed into them, or in this case, placed
into them from code.
You might check and see if the .Find operation is working at all. Change the

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)

section to:
If Not foundItem Is Nothing Then
Stop
Me!TextBox1.Text = foundItem.Offset(0, 2)

Then when the .Find does find a match the code will stop at the Stop
instruction and you can press [F8] to go line by line through the rest of the
code. You can also hover the cursor over the variables and see what their
values are, or use the Immediate Window to print their values. For example,
you can type
? foundItem[Enter]
To see the actual text of the matched entry, or
? foundItem.Address[Enter]
to see it's address, or
? foundItem.Offset(0, 2)[Enter]
to see what the entry is 2 columns over from the foundItem. [Enter] just
means the enter key.
If you still have problems, consider sending me the workbook via email and
I'll try to help more. You can get it to me through (remove spaces)
Help From @ JLatham Site. com
Remind me in the email of what you're trying to accomplish.

 

[BJ&theBear]

If the other controls are just plain text boxes, they don't need a row source
entry.  Text boxes just get data typed into them, or in this case, placed
into them from code.
You might check and see if the .Find operation is working at all.  Change the

 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)

section to:
 If Not foundItem Is Nothing Then
    Stop
    Me!TextBox1.Text = foundItem.Offset(0, 2)

Then when the .Find does find a match the code will stop at the Stop
instruction and you can press [F8] to go line by line through the rest ofthe
code.  You can also hover the cursor over the variables and see what their
values are, or use the Immediate Window to print their values.  For example,
you can type
? foundItem[Enter]
To see the actual text of the matched entry, or
? foundItem.Address[Enter]
to see it's address, or
? foundItem.Offset(0, 2)[Enter]
to see what the entry is 2 columns over from the foundItem. [Enter] just
means the enter key.
If you still have problems, consider sending me the workbook via email and
I'll try to help more.  You can get it to me through (remove spaces)
Help From @ JLatham Site. com
Remind me in the email of what you're trying to accomplish.

Thanks for the help - I still cannot get the textboxes to show
anything - is there any control source or row source entry required in
textbox1 or textbox2?

I really am terribly rusty

Thanks again

.- Hide quoted text -

- Show quoted text -

Thank you soooo much - it eventually worked a treat - I had a comma
instead of a full stop and it did not throw up an error

Thanks once again

BJ