Vlookup done within a userform

TSoren23

New Member
Joined
Sep 11, 2006
Messages
16
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
I did consider using a combobox, but there are about 100 item numbers. I will take a look again at possibly using it.
 
Upvote 0
I did consider using a combobox, but there are about 100 item numbers.
So why couldn't you use a combobox?:eek:
 
Upvote 0

Forum statistics

Threads
1,218,619
Messages
6,143,509
Members
450,492
Latest member
Rusbus1972

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top