Vlookups in a Form

ElectricSkywalker

Board Regular
Joined
May 27, 2002
Messages
112
Hi all,

I have created a User Form for someone to enter data into.

One of my Input Fields is a list drop down box,( which I have called "lsDepartment")which will select the Department the person is in.

I then want a box below, that automatically does a vlookup, and inputs the Manager name for the Department. (I don't want them to have to input this - I just want it to display the Managers Name)

On another sheet, I have a Table which lists the Departments in Column 1 and Managers in Column 2.

I was attempting to use a a text box, with the following code behind it....but it doesn't seem to work. Can anyone offer any suggestions please:

ActiveCell.Formula "=vlookup(lsDepartment, Sheet1!D2:E70,2,False)"

Thanks,
Greg
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Change that to

ActiveCell.Formula "=vlookup(" & lsDepartment & ", Sheet1!D2:E70,2,False)"

because Excel can't read the value of a control directly from a formula, you have to "translate" it to it.
 

ElectricSkywalker

Board Regular
Joined
May 27, 2002
Messages
112
Thanks Juan for correcting my formula, however I am still getting a blank field.
MAybe I am doing something else wrong?

Basically I want my user form to be:

Select Department: e.g.Sales
Manager: e.g. Fred Nurke


The Department List Box is called "lsDepartment"

The Manager field is a Text Box, called "txtManager"....which I double clicked on and entered the code (as discussed above).

What I was expecting to happen - was as soon as the Department was filled in, the Manager Text Box would automatically look up the "lsDepartment" list box that had been highlighted....and up would come the manager.

At the moment - Get Nothing.

Prehaps I am thinking to much in the rehlm of Excel (as in an instant VLOOKUP)....and not VB?
 

Forum statistics

Threads
1,143,915
Messages
5,721,515
Members
422,369
Latest member
redinator

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