Creating a lookup in a userform
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
On 16 Mar, 19:40, JLatham wrote:
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
.- Hide quoted text -
- Show quoted text -
|