Vlookup based on Combobox in a Userform
 

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 . How can I get it to calculate
when I select a name? Also, how to make the second combobox dependant?

Thanks in advance!

Vlookup based on Combobox in a Userform
 

Do you need to blank
cboEmployee.Value = ""


If this post helps click Yes
---------------
Jacob Skaria


"Dagre" wrote:

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 . How can I get it to calculate
when I select a name? Also, how to make the second combobox dependant?

Thanks in advance!

Vlookup based on Combobox in a Userform
 

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

"Jacob Skaria" wrote:

Do you need to blank
cboEmployee.Value = ""


If this post helps click Yes
---------------
Jacob Skaria


"Dagre" wrote:

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 . How can I get it to calculate
when I select a name? Also, how to make the second combobox dependant?

Thanks in advance!

Vlookup based on Combobox in a Userform
 

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

"Dagre" wrote in message
...
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 . How can I get it to
calculate
when I select a name? Also, how to make the second combobox dependant?

Thanks in advance!

Vlookup based on Combobox in a Userform
 

How would I do this in a form?

"Patrick Molloy" wrote:

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

"Dagre" wrote in message
...
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 . How can I get it to
calculate
when I select a name? Also, how to make the second combobox dependant?

Thanks in advance!

Vlookup based on Combobox in a Userform
 

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" wrote in message
...
How would I do this in a form?

"Patrick Molloy" wrote:

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

"Dagre" wrote in message
...
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 . How can I get it to
calculate
when I select a name? Also, how to make the second combobox dependant?

Thanks in advance!

Vlookup based on Combobox in a Userform
 

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

"Patrick Molloy" wrote:

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" wrote in message
...
How would I do this in a form?

"Patrick Molloy" wrote:

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

"Dagre" wrote in message
...
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 . How can I get it to
calculate
when I select a name? Also, how to make the second combobox dependant?

Thanks in advance!