Ok, I have a userform. On this user form I have a combo box that pulls its data from a worksheet called 'Lists' (Range A2:A49). I have three text boxes Purchasing_Group 'Lists' worksheet (Range D2:D49), Plant_Number 'Lists' (Range B2:B49), and Profit_Center 'Lists' (Range E2:E49) combo box.
I need the three text boxes to auto-fill based on the choice from the combo box.
May 07, 2016 Backstory is that I am using Excel 2016 on a Mac. I never use excel but as a Graphic Designer, I've been asked to create forms with drop down menu's as well as some live template options. I'm only concerned with the drop down boxes at the moment.
closed as too broad by DavidPostill♦Jun 22 '17 at 17:41
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
1 Answer
I think this should work, at least it does in Excel 2011.
In the code below, 'ComboResult' is the output of your Combo box (the 'cell link'). It has to be entered as a cell reference in double quotes, i.e. 'C7'.
Similarly 'LookupTable' is the range of cells in your plant table, i.e 'A1:E50'. Again, double quotes. I'm assuming your lookup table is on Sheet2. You can use a named sheet by using Sheets('sheet name').Range().
Because the Combo box outputs a number (the position of the user's choice in the list), your lookup table must have an index column as the first column. It will just be 1,2,3 etc. up to the number of plants in the list.
After adding the index column, the data you want will be in columns 3,4 and 5 of the table, and the vlookup functions grab those values, based on the index chosen in the Combo box.
BandersnatchBandersnatch