Vlookups in a Form

[ElectricSkywalker]

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

 

[Juan Pablo González]

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]

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?