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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Couldn't you just put the values in the formula cells in your worksheet in the other TextBoxes?
 

TSoren23

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Sep 11, 2006
Messages
16

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 11, 2006
Messages
16
I did consider using a combobox, but there are about 100 item numbers. I will take a look again at possibly using it.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
I did consider using a combobox, but there are about 100 item numbers.
So why couldn't you use a combobox?:eek:
 

Forum statistics

Threads
1,141,756
Messages
5,708,332
Members
421,565
Latest member
Lastadiego

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
Top