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

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).

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,181,906
Messages
5,932,730
Members
436,856
Latest member
s4shubro

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