I have a form with different combo boxes.

The row source for each combo box is in different sheets.

For example, I have one sheet for account manages info.

I have 3 columns, ID, AccountManagerName, AccountManagerEmail.

For my combo box, my row source is AccountManagerName, so the names are
displayed.

My code is:

Private Sub AccountManagerName_DropButtonClick()
AccountManagerName.AccountManagerName= "AccountManagerName!B2:B15"
End Sub

My form is dedicated to store info within a sheet and send email. In my code
I need to insert the email address of the AccountManagerName selected from
the combo box.

In my "sendto" statement, how can I selected the email address which relates
to AccountManagerName selected by the user in the combo box ?


Thanks in advance for any help.

Regards

Stephan

MName = AccountManagerName.AccountManagerName
with sheets("AccountManagerName")
set c = Range("B2:B15").find(what:=MName,lookin:=xlvalues, lookat:=xlwhole)
if c is nothing then
msgbox("Could not find Manager : " & MName)
else
MEmail = c.offset(0,1)
end if
end with

"Stephan Leduc" wrote:

I have a form with different combo boxes.

The row source for each combo box is in different sheets.

For example, I have one sheet for account manages info.

I have 3 columns, ID, AccountManagerName, AccountManagerEmail.

For my combo box, my row source is AccountManagerName, so the names are
displayed.

My code is:

Private Sub AccountManagerName_DropButtonClick()
AccountManagerName.AccountManagerName= "AccountManagerName!B2:B15"
End Sub

My form is dedicated to store info within a sheet and send email. In my code
I need to insert the email address of the AccountManagerName selected from
the combo box.

In my "sendto" statement, how can I selected the email address which relates
to AccountManagerName selected by the user in the combo box ?


Thanks in advance for any help.

Regards

Stephan

Thanks Joel for the fast answer.

Where do I write this code ?

Thanks

Stephan

"Joel" wrote:


MName = AccountManagerName.AccountManagerName
with sheets("AccountManagerName")
set c = Range("B2:B15").find(what:=MName,lookin:=xlvalues, lookat:=xlwhole)
if c is nothing then
msgbox("Could not find Manager : " & MName)
else
MEmail = c.offset(0,1)
end if
end with

"Stephan Leduc" wrote:

I have a form with different combo boxes.

The row source for each combo box is in different sheets.

For example, I have one sheet for account manages info.

I have 3 columns, ID, AccountManagerName, AccountManagerEmail.

For my combo box, my row source is AccountManagerName, so the names are
displayed.

My code is:

Private Sub AccountManagerName_DropButtonClick()
AccountManagerName.AccountManagerName= "AccountManagerName!B2:B15"
End Sub

My form is dedicated to store info within a sheet and send email. In my code
I need to insert the email address of the AccountManagerName selected from
the combo box.

In my "sendto" statement, how can I selected the email address which relates
to AccountManagerName selected by the user in the combo box ?


Thanks in advance for any help.

Regards

Stephan

You should be using the linkedcell property of the combobox to fill the
combobox. the click routine is where you would put the code I provided. The
code only gets the email address in the column next to the managers name.
You have to put the variable MEmail into the send to section of your code.

"Stephan Leduc" wrote:

Thanks Joel for the fast answer.

Where do I write this code ?

Thanks

Stephan

"Joel" wrote:


MName = AccountManagerName.AccountManagerName
with sheets("AccountManagerName")
set c = Range("B2:B15").find(what:=MName,lookin:=xlvalues, lookat:=xlwhole)
if c is nothing then
msgbox("Could not find Manager : " & MName)
else
MEmail = c.offset(0,1)
end if
end with

"Stephan Leduc" wrote:

I have a form with different combo boxes.

The row source for each combo box is in different sheets.

For example, I have one sheet for account manages info.

I have 3 columns, ID, AccountManagerName, AccountManagerEmail.

For my combo box, my row source is AccountManagerName, so the names are
displayed.

My code is:

Private Sub AccountManagerName_DropButtonClick()
AccountManagerName.AccountManagerName= "AccountManagerName!B2:B15"
End Sub

My form is dedicated to store info within a sheet and send email. In my code
I need to insert the email address of the AccountManagerName selected from
the combo box.

In my "sendto" statement, how can I selected the email address which relates
to AccountManagerName selected by the user in the combo box ?


Thanks in advance for any help.

Regards

Stephan

Joel:

If I have my rep name in row B and I have their email address in row and I
want my form to show the rep name in the combo box but I want to select the
email in row C, how can I do this using the linked cell function in Excel ?

Thanks

Stephan

"Joel" wrote:

You should be using the linkedcell property of the combobox to fill the
combobox. the click routine is where you would put the code I provided. The
code only gets the email address in the column next to the managers name.
You have to put the variable MEmail into the send to section of your code.

"Stephan Leduc" wrote:

Thanks Joel for the fast answer.

Where do I write this code ?

Thanks

Stephan

"Joel" wrote:


MName = AccountManagerName.AccountManagerName
with sheets("AccountManagerName")
set c = Range("B2:B15").find(what:=MName,lookin:=xlvalues, lookat:=xlwhole)
if c is nothing then
msgbox("Could not find Manager : " & MName)
else
MEmail = c.offset(0,1)
end if
end with

"Stephan Leduc" wrote:

I have a form with different combo boxes.

The row source for each combo box is in different sheets.

For example, I have one sheet for account manages info.

I have 3 columns, ID, AccountManagerName, AccountManagerEmail.

For my combo box, my row source is AccountManagerName, so the names are
displayed.

My code is:

Private Sub AccountManagerName_DropButtonClick()
AccountManagerName.AccountManagerName= "AccountManagerName!B2:B15"
End Sub

My form is dedicated to store info within a sheet and send email. In my code
I need to insert the email address of the AccountManagerName selected from
the combo box.

In my "sendto" statement, how can I selected the email address which relates
to AccountManagerName selected by the user in the combo box ?


Thanks in advance for any help.

Regards

Stephan

Something like this. I would use a listbox instead of a combobox.

Private Sub ListBox1_Change()
off = ListBox1.ListIndex
email = Sheets("Sheet1").Range("B1").Offset(off, 1)
End Sub

Private Sub UserForm_Initialize()
ListBox1.Clear
ListBox1.RowSource = "Sheet1!B1:B7"
End Sub

"Stephan Leduc" wrote:

Joel:

If I have my rep name in row B and I have their email address in row and I
want my form to show the rep name in the combo box but I want to select the
email in row C, how can I do this using the linked cell function in Excel ?

Thanks

Stephan

"Joel" wrote:

You should be using the linkedcell property of the combobox to fill the
combobox. the click routine is where you would put the code I provided. The
code only gets the email address in the column next to the managers name.
You have to put the variable MEmail into the send to section of your code.

"Stephan Leduc" wrote:

Thanks Joel for the fast answer.

Where do I write this code ?

Thanks

Stephan

"Joel" wrote:


MName = AccountManagerName.AccountManagerName
with sheets("AccountManagerName")
set c = Range("B2:B15").find(what:=MName,lookin:=xlvalues, lookat:=xlwhole)
if c is nothing then
msgbox("Could not find Manager : " & MName)
else
MEmail = c.offset(0,1)
end if
end with

"Stephan Leduc" wrote:

I have a form with different combo boxes.

The row source for each combo box is in different sheets.

For example, I have one sheet for account manages info.

I have 3 columns, ID, AccountManagerName, AccountManagerEmail.

For my combo box, my row source is AccountManagerName, so the names are
displayed.

My code is:

Private Sub AccountManagerName_DropButtonClick()
AccountManagerName.AccountManagerName= "AccountManagerName!B2:B15"
End Sub

My form is dedicated to store info within a sheet and send email. In my code
I need to insert the email address of the AccountManagerName selected from
the combo box.

In my "sendto" statement, how can I selected the email address which relates
to AccountManagerName selected by the user in the combo box ?


Thanks in advance for any help.

Regards

Stephan