Creating a lookup in a userform
in [General]
|
Prev: HOW CAN I ASSIGN NUMBERS TO CONTRACTS AUTOMATICALLY
Next: How do I delete merged cells in Excel without deleting the final d
From: BJ&theBear on 16 Mar 2010 10:40 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
From: JLatham on 16 Mar 2010 15:40 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 > . >
From: BJ&theBear on 16 Mar 2010 17:39 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 <JLat...(a)discussions.microsoft.com> 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 -
From: JLatham on 17 Mar 2010 00:15 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 <JLat...(a)discussions.microsoft.com> 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 - > > . >
From: BJ&theBear on 17 Mar 2010 14:51
On Mar 17, 4:15 am, JLatham <JLat...(a)discussions.microsoft.com> 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 <JLat...(a)discussions.microsoft.com> 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 |