Vlookup based on Combobox in a Userform

[Dagre]

I want to be able to select a department from the drop down list in a
combobox ("Department"), which will then reduce the list in the second combo
box ("Name"). Then, when an employee name has been selected, lookup the email
address in the spreadsheet, and automatically enter it into a text box
("Email").
I'm not sure how to implement the dependant combo box. I did try the vlookup
in the email box, but it shows the error: "Unable to get the VLookup property
of the WorksheetFunction class."

Here is what I have so far:

Private Sub UserForm_Initialize()
Dim cEmployee As Range
Dim ws As Worksheet
Set ws = Worksheets("ValidationData")

txtSubject.Value = ""
txtDueDate.Value = ""
chkReminder = False
txtReminderTime.Value = ""

For Each cEmployee In ws.Range("Employee")
With Me.cboEmployee
.AddItem cEmployee.Value
.List(.ListCount - 1, 1) = cEmployee.Offset(0, 1).Value
End With
Next cEmployee
cboEmployee.Value = ""
txtEmail.Value = WorksheetFunction.VLookup(cboEmployee,
Range("Email"), 2, False)
txtNotes.Value = ""
txtSubject.SetFocus
End Sub

If I comment out the vlookup function, the whole thing works, I just have to
enter emails manually. If I change cboEmployee.Value = "A Brooks", it works,
and picks up the email address. However, if (using the form) I select another
name, it sticks with (e-mail address removed). How can I get it to calculate
when I select a name? Also, how to make the second combobox dependant?

Thanks in advance!

 

[Jacob Skaria]

Do you need to blank
cboEmployee.Value = ""


If this post helps click Yes

 

[Dagre]

Not necessarily, do you think that could be causing the problem?

 

[Patrick Molloy]

it sounds like Data/ Filter /Autofilter should be what you need to use.

 

[Dagre]

How would I do this in a form?

it sounds like Data/ Filter /Autofilter should be what you need to use.

 

[Patrick Molloy]

have all the data in a hidden list
use a textbox for the filter
each time there's a change to the textbox, populate a 2nd, visible, list
from the hidden table, filtered by the textbox
should be quite quick if there aren't too many items.

 

[Dagre]

I'm afraid you've lost me.
I have the data in a hidden sheet at the minute, with range names.
There isn't much data there, just a short list of names (approx 30 names),
with department and email address next to it.
I wasn't aware you could use a textbox for a filter, can you explain further?

Thanks for all you help