Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLookup on user form (VBA)
In my workbook I have an "Employees" sheet. Column A is headed
'Payroll Number' and column B is 'Employee Name'. I then have a combobox in a userform which takes the Payroll Number from the Employees sheet. To the side of this combo box I have a text box to store the employee name. When I select a payroll number I need the employee name to be automatically populated in the employee name text box using a vlookup(?) from the "Employees" sheet. My VB skills are limited so looking for a solution if anyone can help please. Many thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLookup on user form (VBA)
Could you supply a cut down version of your workbook to save us having to reproduce your scenario? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143147 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLookup on user form (VBA)
Of course. File can be downloaded from http://sites.google.com/site/lucasreece/files.
Download issues.xls Clicking on the new issue command button opens the user form. Issue ID is automatically generated. Use the combo box to select an employee payroll number which is taken from the Empoyees sheet. When a payroll number has been selected, I need the name text box to display (using a lookup maybe?) the name for the selected payroll number from the Employees sheet. Hope that makes sense. Thanks. On 10 Oct, 21:40, p45cal wrote: Could you supply a cut down version of your workbook to save us having to reproduce your scenario? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=143147 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLookup on user form (VBA)
UPDATE:
I've added this to the vba code... Private Sub cboPayrollNumber_Change() Me.txtName = WorksheetFunction.VLookup(Me.cboPayrollNumber, _ Worksheets("Employees").Range("A:D"), 4, 0) End Sub Now, when I click on the new issue button I get this... 'Run-time error: '1004': Unable to get the VLookup property of the WorksheetFunction class. Any ideas please? Need resolving quite urgently now if someone could help me out please. Thank you. On 10 Oct, 23:12, Lucas Reece wrote: Of course. File can be downloaded fromhttp://sites.google.com/site/lucasreece/files. Download issues.xls Clicking on the new issue command button opens the user form. Issue ID is automatically generated. Use the combo box to select an employee payroll number which is taken from the Empoyees sheet. When a payroll number has been selected, I need the name text box to display (using a lookup maybe?) the name for the selected payroll number from the Employees sheet. Hope that makes sense. Thanks. On 10 Oct, 21:40, p45cal wrote: Could you supply a cut down version of your workbook to save us having to reproduce your scenario? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=143147 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLookup on user form (VBA)
Hi,
Add this change event code to cboPayrollNumber and every time you change the payrol number the employee name is added to the text box called txtName. Because you clear the combobox in the ADD button code we need on On Error statement. Note I convert the lookup to a value because the combobox is returning a string Private Sub cboPayrollNumber_Change() Dim LastRow As Long On Error Resume Next LastRow = Sheets("Employees").Cells(Cells.Rows.Count, "A").End(xlUp).Row txtName.Text = WorksheetFunction.VLookup(Val(cboPayrollNumber.Tex t), _ Sheets("Employees").Range("A2:B" & LastRow), 2, False) End Sub Mike "Lucas Reece" wrote: UPDATE: I've added this to the vba code... Private Sub cboPayrollNumber_Change() Me.txtName = WorksheetFunction.VLookup(Me.cboPayrollNumber, _ Worksheets("Employees").Range("A:D"), 4, 0) End Sub Now, when I click on the new issue button I get this... 'Run-time error: '1004': Unable to get the VLookup property of the WorksheetFunction class. Any ideas please? Need resolving quite urgently now if someone could help me out please. Thank you. On 10 Oct, 23:12, Lucas Reece wrote: Of course. File can be downloaded fromhttp://sites.google.com/site/lucasreece/files. Download issues.xls Clicking on the new issue command button opens the user form. Issue ID is automatically generated. Use the combo box to select an employee payroll number which is taken from the Empoyees sheet. When a payroll number has been selected, I need the name text box to display (using a lookup maybe?) the name for the selected payroll number from the Employees sheet. Hope that makes sense. Thanks. On 10 Oct, 21:40, p45cal wrote: Could you supply a cut down version of your workbook to save us having to reproduce your scenario? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=143147 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLookup on user form (VBA)
Excellent! That worked. Thanks for that Mike.
However I now have another issue so hoping you can help if that's OK? When I click the Add button after creating a new issue, the message box is displayed saying issue complete which if fine but then when I click OK, I get a message box displayed saying "Invalid Property Value"! I've google this afternoon but can't seem to find a solution to this. Any ideas please? New file at http://sites.google.com/site/lucasreece/files. Download issues.xls Many thanks. On 11 Oct, 11:20, Mike H wrote: Hi, Add this change event code to cboPayrollNumber and every time you change the payrol number the employee name is added to the text box called txtName. Because you clear the combobox in the ADD button code we need on On Error statement. Note I convert the lookup to a value because the combobox is returning a string Private Sub cboPayrollNumber_Change() Dim LastRow As Long On Error Resume Next LastRow = Sheets("Employees").Cells(Cells.Rows.Count, "A").End(xlUp).Row txtName.Text = WorksheetFunction.VLookup(Val(cboPayrollNumber.Tex t), _ Sheets("Employees").Range("A2:B" & LastRow), 2, False) End Sub Mike "Lucas Reece" wrote: UPDATE: I've added this to the vba code... Private Sub cboPayrollNumber_Change() * * Me.txtName = WorksheetFunction.VLookup(Me.cboPayrollNumber, _ * * Worksheets("Employees").Range("A:D"), 4, 0) End Sub Now, when I click on the new issue button I get this... 'Run-time error: '1004': Unable to get the VLookup property of the WorksheetFunction class. Any ideas please? Need resolving quite urgently now if someone could help me out please. Thank you. On 10 Oct, 23:12, Lucas Reece wrote: Of course. File can be downloaded fromhttp://sites.google.com/site/lucasreece/files. Download issues.xls Clicking on the new issue command button opens the user form. Issue ID is automatically generated. Use the combo box to select an employee payroll number which is taken from the Empoyees sheet. When a payroll number has been selected, I need the name text box to display (using a lookup maybe?) the name for the selected payroll number from the Employees sheet. Hope that makes sense. Thanks. On 10 Oct, 21:40, p45cal wrote: Could you supply a cut down version of your workbook to save us having to reproduce your scenario? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=143147 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLookup on user form (VBA)
Can anyone offer any assistance with this one please?
Many thanks. On 11 Oct, 19:19, Lucas Reece wrote: Excellent! That worked. Thanks for that Mike. However I now have another issue so hoping you can help if that's OK? When I click the Add button after creating a new issue, the message box is displayed saying issue complete which if fine but then when I click OK, I get a message box displayed saying "Invalid Property Value"! I've google this afternoon but can't seem to find a solution to this. Any ideas please? New file athttp://sites.google.com/site/lucasreece/files. Download issues.xls Many thanks. On 11 Oct, 11:20, Mike H wrote: Hi, Add this change event code to cboPayrollNumber and every time you change the payrol number the employee name is added to the text box called txtName.. Because you clear the combobox in the ADD button code we need on On Error statement. Note I convert the lookup to a value because the combobox is returning a string Private Sub cboPayrollNumber_Change() Dim LastRow As Long On Error Resume Next LastRow = Sheets("Employees").Cells(Cells.Rows.Count, "A").End(xlUp).Row txtName.Text = WorksheetFunction.VLookup(Val(cboPayrollNumber.Tex t), _ Sheets("Employees").Range("A2:B" & LastRow), 2, False) End Sub Mike "Lucas Reece" wrote: UPDATE: I've added this to the vba code... Private Sub cboPayrollNumber_Change() * * Me.txtName = WorksheetFunction.VLookup(Me.cboPayrollNumber, _ * * Worksheets("Employees").Range("A:D"), 4, 0) End Sub Now, when I click on the new issue button I get this... 'Run-time error: '1004': Unable to get the VLookup property of the WorksheetFunction class. Any ideas please? Need resolving quite urgently now if someone could help me out please. Thank you. On 10 Oct, 23:12, Lucas Reece wrote: Of course. File can be downloaded fromhttp://sites.google.com/site/lucasreece/files. Download issues.xls Clicking on the new issue command button opens the user form. Issue ID is automatically generated. Use the combo box to select an employee payroll number which is taken from the Empoyees sheet. When a payroll number has been selected, I need the name text box to display (using a lookup maybe?) the name for the selected payroll number from the Employees sheet. Hope that makes sense. Thanks. On 10 Oct, 21:40, p45cal wrote: Could you supply a cut down version of your workbook to save us having to reproduce your scenario? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=143147 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLookup on user form (VBA)
Lucas Reece;522496 Wrote: Can anyone offer any assistance with this one please? Many thanks. On 11 Oct, 19:19, Lucas Reece wrote: Excellent! That worked. Thanks for that Mike. However I now have another issue so hoping you can help if that's OK? When I click the Add button after creating a new issue, the message box is displayed saying issue complete which if fine but then when I click OK, I get a message box displayed saying "Invalid Property Value"! I've google this afternoon but can't seem to find a solution to this. Any ideas please? New file athttp://sites.google.com/site/lucasreece/files. Download issues.xls Many thanks. The Invalid property error you're getting is from one of the comboboxes which while clearing the data you set to "". Since the MatchRequired property is set to True and "" is not in the dropdown list - it complains when the control loses focus. Several solutions: One possibility is to set the MatchRequired property to False for both comboboxes. This coulds lead to problems when users try to enter their own values which aren't in the list. Another possibility is to add the "" as an item to each combobox, say at the top of the list. For some reason if you use the line Me.cboProductID.Value = "" to try to set it to that value, it doesn't work properly, but this does: Me.cboPayrollNumber.ListIndex = 0 if, of course, the "" is at the top. Ensure this by making it the first item with the line: Me.cboPayrollNumber.AddItem "" immediately befo For Each cPayrollNumber In.... in the initialising routine. (the same for the other combobox). (By the way, I did try to set the .listindex to -1, but that didn't work either.) You will probably come up against a new problem, now that the code is being allowed to run beyond the Invalid Property error, and that is the change/afterUpdate events associated with some of the other controls which try to calculate things.. - some more if statements in those event handlers for you to code! As an aside, I notice that you populate 2 columns in each combobox. Instead of using a vlookup to return the description/name, you could use the .value property of the combox if the bound column is set to 2. Take a look at the Remarks section in vba help for the TextColumn property and the BoundColumn property of comboboxes. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143147 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLookup on user form (VBA)
Curious..you ask a question, you prompt again for an answer 17 hours later, you get my response less than 3 hours after that.. 46 hours after that and you seem to have lost interest. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143147 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLookup on user form (VBA)
Sorry p45cal.
I've been struggling to get on PC to be honest and I've not been too well today :o( I'm not purposely ignoring the help that anyone and especially your good self has given on this thread. Thanks for your help on this. On 14 Oct, 13:16, p45cal wrote: Curious..you ask a question, you prompt again for an answer 17 hours later, you get my response less than 3 hours after that.. 46 hours after that and you seem to have lost interest. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=143147 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date field in user form & Loading a user form on opening workbook | Excel Programming | |||
Automatically add a textbox to a user form based on user requireme | Excel Programming | |||
User Form Vlookup help? | Excel Programming | |||
Excel VB User Form Using Vlookup? | Excel Programming | |||
How to: User Form to assign a user defined range to a macro variab | Excel Programming |