Creating a lookup in a userform
On Mar 17, 4:15*am, JLatham wrote:
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" wrote:
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 -
.- 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
|