Vlookup done within a userform

[TSoren23]

Hello,
I have designed a user form that a supervisor will enter data from a daily production operation. I have tried to make it as user friendly as possible for them. The supervisor only will need to enter the "Item number" which will look up data in another workbook and populate some of the other txt boxs on the form. He will enter a value in a text box (txtItem) on the userform and have it return a value in the other text boxs in the userform (txtProduct), (txtProC), and (txtOpt).

Currently I have a spread sheet that will do a vlookup within the worksheet but I wold like to have this all happen within the userform.

Can anyone show what the code would be do the same operations in a userform.

Below is my Vlookup formula within the worksheet.

Product;(txtProduct)
=IF(B4="","",VLOOKUP(B4,'\\Owensrv\owen\Management Projects\Plant Governance\[Rate Lookup Tool.xls]North'!$A:$D,2,FALSE))

Product Cat; (txtProC)
=IF(B4="","",VLOOKUP(B4,'\\Owensrv\owen\Management Projects\Plant Governance\[Rate Lookup Tool.xls]North'!$A:$D,4,FALSE))

Optimal; (txtOpt)
=VLOOKUP(E4,'\\Owensrv\owen\Management Projects\Plant Governance\[Rate Lookup Tool.xls]North'!$F:$I,4,FALSE)
Thank you

 

[Andrew Poulsom]

Couldn't you just put the values in the formula cells in your worksheet in the other TextBoxes?

 

[TSoren23]

I really had not thought about doing it that way. The goal I had was to only place values into the worksheets. right now with all the formulas I have in the WS it really slows the input process down with each step. I will have over 3000 rows of data when all finished. What I had hopped to do was have all calculations and look ups within the userform. currently now the way I have it written, once all the input data is completed the user will press a control that will send the values to the WS and then clear the txtboxes for the next set of data. (Item)

 

[Andrew Poulsom]

I don't think having 3 VLOOKUP formulas on a worksheet rather than trying to calculate them in VBA is going to slow the process down.

 

[TSoren23]

Ok I guess that could work.
Next question, how would I display the cell values; (txtProduct), (txtProC), (txtOpt), in the userform after the Item number is typed in?

 

[Norie]

Have you considered using a combobox rather than a textbox?

If you did you might be able to use it's ListIndex property to return the other values.

For that to work would depend on how you populated the combobox.

Another advantage of the combobox could be that the user would be restricted to only selecting existing item numbers.

 

[TSoren23]

I did consider using a combobox, but there are about 100 item numbers. I will take a look again at possibly using it.

 

[Norie]

I did consider using a combobox, but there are about 100 item numbers.

So why couldn't you use a combobox?